PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Writer\Exception

Unknown range separator

Error message

Unknown range separator

What it means

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'.

Source

Thrown at src/PhpSpreadsheet/Writer/Xls/Parser.php:655

        // Variable number of args eg. SUM($i, $j, $k, ..).
        return pack('CCv', $this->ptg['ptgFuncVarV'], $num_args, $this->functions[$token][0]);
    }

    /**
     * Convert an Excel range such as A1:D4 to a ptgRefV.
     *
     * @param string $range An Excel range in the A1:A2
     */
    private function convertRange2d(string $range, int $class = 0): string
    {
        // TODO: possible class value 0,1,2 check Formula.pm
        // Split the range into 2 cell refs
        if (Preg::isMatch('/^(\$)?([A-Ia-i]?[A-Za-z])(\$)?(\d+)\:(\$)?([A-Ia-i]?[A-Za-z])(\$)?(\d+)$/', $range)) {
            [$cell1, $cell2] = explode(':', $range);
        } else {
            // TODO: use real error codes
            throw new WriterException('Unknown range separator');
        }
        // Convert the cell references
        [$row1, $col1] = $this->cellToPackedRowcol($cell1);
        [$row2, $col2] = $this->cellToPackedRowcol($cell2);

        // The ptg value depends on the class of the ptg.
        if ($class == 0) {
            $ptgArea = pack('C', $this->ptg['ptgArea']);
        } elseif ($class == 1) {
            $ptgArea = pack('C', $this->ptg['ptgAreaV']);
        } elseif ($class == 2) {
            $ptgArea = pack('C', $this->ptg['ptgAreaA']);
        } else {
            // TODO: use real error codes
            throw new WriterException("Unknown class $class");
        }

        return $ptgArea . $row1 . $row2 . $col1 . $col2;

View on GitHub (pinned to 65b080eef4)

Solutions

1. Replace whole-column/whole-row references with explicit bounded ranges: =SUM(A1:A10000).
2. Compute the used range from getHighestRow()/getHighestColumn() and generate bounded references programmatically.
3. Save as Xlsx if whole-column references must be preserved verbatim.

Example fix

// before
$sheet->getCell('B1')->setValue('=SUM(A:A)');
(new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet))->save('out.xls'); // Unknown range separator

// after
$lastRow = $sheet->getHighestRow();
$sheet->getCell('B1')->setValue("=SUM(A1:A{$lastRow})");

/** Reject whole-column / whole-row references that convertRange2d() cannot parse. */
function formulasUseBoundedRanges(Worksheet $sheet): bool
{
    foreach ($sheet->getCoordinates() as $coord) {
        $v = $sheet->getCell($coord)->getValue();
        if (is_string($v) && $v[0] === '='
            && (preg_match('/(?<![\w$!])([A-Za-z]{1,3}):([A-Za-z]{1,3})(?![\d])/', $v)
                || preg_match('/(?<![\w$!])(\d+):(\d+)/', $v))
        ) {
            return false;
        }
    }

    return true;
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Unknown token $token
• ')' token expected.
• Syntax error: $currentToken, lookahead: $lookAhead, current
• Cannot yet write formulae with defined names to Xls
• Unknown sheet name $ext_ref in formula

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/e23fbcecadb0f0c8. Report an issue: GitHub.