PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Exception

Invalid R1C1-format Cell Reference

Error message

Invalid R1C1-format Cell Reference

What it means

Cell\AddressHelper::convertToA1() parses an R1C1-style reference against a row/column regex (locale-aware, e.g. '/^(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))$/i'). If the address does not match - missing C part, stray characters, wrong order - it throws PhpOffice\PhpSpreadsheet\Exception 'Invalid R1C1-format Cell Reference'. It converts a single R1C1 cell reference to A1 given the current row/column anchor.

Source

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

        return [$rowChar, $colChar];
    }

    /**
     * Converts an R1C1 format cell address to an A1 format cell address.
     */
    public static function convertToA1(
        string $address,
        int $currentRowNumber = 1,
        int $currentColumnNumber = 1,
        bool $useLocale = true
    ): string {
        [$rowChar, $colChar] = $useLocale ? self::getRowAndColumnChars() : ['R', 'C'];
        $regex = '/^(' . $rowChar . '(\[?[-+]?\d*\]?))(' . $colChar . '(\[?[-+]?\d*\]?))$/i';
        $validityCheck = preg_match($regex, $address, $cellReference);

        if (empty($validityCheck)) {
            throw new Exception('Invalid R1C1-format Cell Reference');
        }

        $rowReference = $cellReference[2];
        //    Empty R reference is the current row
        if ($rowReference === '') {
            $rowReference = (string) $currentRowNumber;
        }
        //    Bracketed R references are relative to the current row
        if ($rowReference[0] === '[') {
            $rowReference = $currentRowNumber + (int) trim($rowReference, '[]');
        }
        $columnReference = $cellReference[4];
        //    Empty C reference is the current column
        if ($columnReference === '') {
            $columnReference = (string) $currentColumnNumber;
        }
        //    Bracketed C references are relative to the current column
        if ($columnReference[0] === '[') {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Validate/normalize the R1C1 string before calling: full 'R[n]C[m]' with optional signed bracketed offsets
2. If input may be A1, detect and route to convertToR1C1 instead
3. Pass correct $currentRowNumber/$currentColumnNumber anchors for relative forms like 'R[1]C[-2]'
4. Use AddressHelper::R1C1_COORDINATE_REGEX (or a strict '^R...C...$' anchored pattern) for pre-validation; wrap the call in try/catch PhpOffice\PhpSpreadsheet\Exception

Example fix

// before
$a1 = AddressHelper::convertToA1($ref, 5, 2); // $ref = 'R5' -> throws

// after
if (Preg::isMatch('/^R(\[-?\d+\]|\d*)C(\[-?\d+\]|\d*)$/i', $ref)) {
    $a1 = AddressHelper::convertToA1($ref, 5, 2);
} else {
    throw new InvalidArgumentException("Bad R1C1 ref: $ref");
}

if (!preg_match('/^R(\[-?\d+\]|\d*)C(\[-?\d+\]|\d*)$/i', $ref)) {
    throw new InvalidArgumentException("not an R1C1 reference: $ref");
}
$a1 = AddressHelper::convertToA1($ref, $row, $col);

function isR1C1Ref(string $s): bool
{
    return (bool) preg_match('/^R(\[-?\d+\]|\d*)C(\[-?\d+\]|\d*)$/i', $s);
}

try {
    $a1 = AddressHelper::convertToA1($ref, $row, $col);
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // 'Invalid R1C1-format Cell Reference' -> reject input, log ref and anchors
    throw new InvalidArgumentException($e->getMessage(), 0, $e);
}

When it happens

Trigger: convertToA1('R5') or convertToA1('C3') (half a reference); convertToA1('Z5C2'); convertToA1('R5C2X'); localized input when $useLocale expects different row/col initials; passing an A1 string like 'B3' by mistake; empty string input.

Common situations: Converting R1C1 formulas from Xlsx/Xml sources or non-Excel tools; accepting R1C1 coordinates from user input or URLs without validation; locale spreadsheets (row/column indicator letters differ, e.g. Cyrillic) processed with the wrong $useLocale flag; mixing up convertToA1/convertToR1C1 direction.

Related errors

• Invalid A1-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/4a4c68ba42b6d32f. Report an issue: GitHub.