{"record":{"id":"e23fbcecadb0f0c8","repo":"PHPOffice/PhpSpreadsheet","slug":"unknown-range-separator","errorCode":null,"errorMessage":"Unknown range separator","messagePattern":"Unknown range separator","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Writer\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Writer/Xls/Parser.php","lineNumber":655,"sourceCode":"\n        // Variable number of args eg. SUM($i, $j, $k, ..).\n        return pack('CCv', $this->ptg['ptgFuncVarV'], $num_args, $this->functions[$token][0]);\n    }\n\n    /**\n     * Convert an Excel range such as A1:D4 to a ptgRefV.\n     *\n     * @param string $range An Excel range in the A1:A2\n     */\n    private function convertRange2d(string $range, int $class = 0): string\n    {\n        // TODO: possible class value 0,1,2 check Formula.pm\n        // Split the range into 2 cell refs\n        if (Preg::isMatch('/^(\\$)?([A-Ia-i]?[A-Za-z])(\\$)?(\\d+)\\:(\\$)?([A-Ia-i]?[A-Za-z])(\\$)?(\\d+)$/', $range)) {\n            [$cell1, $cell2] = explode(':', $range);\n        } else {\n            // TODO: use real error codes\n            throw new WriterException('Unknown range separator');\n        }\n        // Convert the cell references\n        [$row1, $col1] = $this->cellToPackedRowcol($cell1);\n        [$row2, $col2] = $this->cellToPackedRowcol($cell2);\n\n        // The ptg value depends on the class of the ptg.\n        if ($class == 0) {\n            $ptgArea = pack('C', $this->ptg['ptgArea']);\n        } elseif ($class == 1) {\n            $ptgArea = pack('C', $this->ptg['ptgAreaV']);\n        } elseif ($class == 2) {\n            $ptgArea = pack('C', $this->ptg['ptgAreaA']);\n        } else {\n            // TODO: use real error codes\n            throw new WriterException(\"Unknown class $class\");\n        }\n\n        return $ptgArea . $row1 . $row2 . $col1 . $col2;","sourceCodeStart":637,"sourceCodeEnd":673,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Writer/Xls/Parser.php#L637-L673","documentation":"convertRange2d() accepts only strictly two-cell A1-style ranges matching '(\\$)?[col](\\$)?[row]:(\\$)?[col](\\$)?[row]' (e.g. A1:D4, $B$2:$C$9). Any range token with a different shape - whole-column (A:B), whole-row (1:2), ranges with sheet qualifiers arriving unsplit, or extra segments - fails the regex and throws 'Unknown range separator'.","triggerScenarios":"Formulas containing whole-column or whole-row references such as =SUM(A:A) or =COUNT(1:1) in a workbook saved as Xls; ranges whose separator segment count is not exactly one colon between two fully specified cell refs.","commonSituations":"Copy-pasting modern spreadsheet formulas (where whole-column refs are idiomatic) into templates exported as legacy Xls; import pipelines that take user formulas verbatim and re-save to a BIFF8-compatible format required by an old ERP.","solutions":["Replace whole-column/whole-row references with explicit bounded ranges: =SUM(A1:A10000).","Compute the used range from getHighestRow()/getHighestColumn() and generate bounded references programmatically.","Save as Xlsx if whole-column references must be preserved verbatim."],"exampleFix":"// before\n$sheet->getCell('B1')->setValue('=SUM(A:A)');\n(new \\PhpOffice\\PhpSpreadsheet\\Writer\\Xls($spreadsheet))->save('out.xls'); // Unknown range separator\n\n// after\n$lastRow = $sheet->getHighestRow();\n$sheet->getCell('B1')->setValue(\"=SUM(A1:A{$lastRow})\");","handlingStrategy":"validation","validationCode":"/** Reject whole-column / whole-row references that convertRange2d() cannot parse. */\nfunction formulasUseBoundedRanges(Worksheet $sheet): bool\n{\n    foreach ($sheet->getCoordinates() as $coord) {\n        $v = $sheet->getCell($coord)->getValue();\n        if (is_string($v) && $v[0] === '='\n            && (preg_match('/(?<![\\w$!])([A-Za-z]{1,3}):([A-Za-z]{1,3})(?![\\d])/', $v)\n                || preg_match('/(?<![\\w$!])(\\d+):(\\d+)/', $v))\n        ) {\n            return false;\n        }\n    }\n\n    return true;\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["When generating SUM/aggregation formulas, always emit explicit endpoints from getHighestRow()/getHighestColumn().","Document for users that whole-column refs (A:A) are invalid in the legacy Xls output path.","Prefer Xlsx when fidelity for modern formula idioms matters."],"tags":["xls","biff8","formula","range","whole-column","parser"],"backgroundTag":"invalid-range-format","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}