PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Invalid A1-format Cell Reference

Error message

Invalid A1-format Cell Reference

What it means

Cell\AddressHelper::convertToR1C1() first validates the input against Coordinate::A1_COORDINATE_REGEX (optionally $-absolute like '$B$3'). Anything that is not a single A1 cell address - a range ('A1:B2'), a bare column ('A'), reversed garbage ('1A'), a defined name, an empty string - throws PhpOffice\PhpSpreadsheet\Exception 'Invalid A1-format Cell Reference'.

Source

Thrown at src/PhpSpreadsheet/Cell/AddressHelper.php:140

            }
        }
        unset($value);

        //    Then rebuild the formula string
        return implode('"', $temp);
    }

    /**
     * Converts an A1 format cell address to an R1C1 format cell address.
     * If $currentRowNumber or $currentColumnNumber are provided, then the R1C1 address will be formatted as a relative address.
     */
    public static function convertToR1C1(
        string $address,
        ?int $currentRowNumber = null,
        ?int $currentColumnNumber = null
    ): string {
        if (1 !== preg_match(Coordinate::A1_COORDINATE_REGEX, $address, $cellReference)) {
            throw new Exception('Invalid A1-format Cell Reference');
        }

        if ($cellReference['col'][0] === '$') {
            // Column must be absolute address
            $currentColumnNumber = null;
        }
        $columnId = Coordinate::columnIndexFromString(ltrim($cellReference['col'], '$'));

        if ($cellReference['row'][0] === '$') {
            // Row must be absolute address
            $currentRowNumber = null;
        }
        $rowId = (int) ltrim($cellReference['row'], '$');

        if ($currentRowNumber !== null) {
            if ($rowId === $currentRowNumber) {
                $rowId = '';
            } else {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Validate first with the same rule the library uses: preg_match(Coordinate::A1_COORDINATE_REGEX, $address)
2. Split ranges into cell pairs via Coordinate::extractAllCellReferencesInRange() and convert each cell
3. Strip sheet-qualified prefixes ('Sheet1!A1' -> 'A1') and trim whitespace/$ handling before converting
4. Wrap in try/catch PhpOffice\PhpSpreadsheet\Exception and reject bad addresses explicitly

Example fix

// before
foreach (['A1:B2', 'C3'] as $a) { AddressHelper::convertToR1C1($a); } // 'A1:B2' throws

// after
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
foreach (['A1:B2', 'C3'] as $a) {
    foreach (Coordinate::extractAllCellReferencesInRange($a) as $cell) {
        $r1c1 = AddressHelper::convertToR1C1($cell);
    }
}

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

if (!preg_match(Coordinate::A1_COORDINATE_REGEX, $address)) {
    throw new InvalidArgumentException("not a single A1 address: $address");
}
$r1c1 = AddressHelper::convertToR1C1($address);

function isSingleA1Address(string $s): bool
{
    return (bool) preg_match(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::A1_COORDINATE_REGEX, $s);
}

try {
    $r1c1 = AddressHelper::convertToR1C1($addr);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // 'Invalid A1-format Cell Reference' -> normalize input (strip sheet prefix,
    // split ranges) and retry, or reject
}

When it happens

Trigger: AddressHelper::convertToR1C1('A1:B2') (range instead of single cell); convertToR1C1('1A') or convertToR1C1('AB') or convertToR1C1(''); passing an R1C1 string ('R2C3') by mistake; defined names or sheet-qualified refs ('Sheet1!A1') reaching the function unsplit.

Common situations: Converting addresses from user input or CSV columns without normalizing; looping over extracted range strings instead of their individual cells; forgetting to strip sheet prefixes before conversion; direction confusion between the two converters.

Related errors

• Invalid R1C1-format Cell Reference
• Invalid R1C1-format Cell Reference, Value out of range
• #VALUE!
• $title!$coordinate -> $message
• File doesn't seem to be an OLE container.

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