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

Formula Error: No closing ']' to match opening '['

Error message

Formula Error: No closing ']' to match opening '['

What it means

When the formula lexer meets '[' it consumes the rest as a table structured reference and scans forward for the matching ']'. StructuredReference::fromParser() throws this error if the formula ends before a closing bracket is found, because the reference cannot be delimited. It is a parse-time failure tied to Excel table (ListObject) syntax such as =SUM(Sales[Amount]).

Source

Thrown at src/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php:71

    public function __construct(string $structuredReference)
    {
        $this->value = $structuredReference;
    }

    /** @param string[] $matches */
    public static function fromParser(string $formula, int $index, array $matches): self
    {
        $val = $matches[0];

        $srCount = substr_count($val, self::OPEN_BRACE)
            - substr_count($val, self::CLOSE_BRACE);
        while ($srCount > 0) {
            $srIndex = strlen($val);
            $srStringRemainder = substr($formula, $index + $srIndex);
            $closingPos = strpos($srStringRemainder, self::CLOSE_BRACE);
            if ($closingPos === false) {
                throw new Exception("Formula Error: No closing ']' to match opening '['");
            }
            $srStringRemainder = substr($srStringRemainder, 0, $closingPos + 1);
            --$srCount;
            if (str_contains($srStringRemainder, self::OPEN_BRACE)) {
                ++$srCount;
            }
            $val .= $srStringRemainder;
        }

        return new self($val);
    }

    /**
     * @throws Exception
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public function parse(Cell $cell): string
    {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Balance the brackets in the structured reference: every '[' needs its ']'.
2. Pre-check formula strings before calculation: substr_count($formula, '[') === substr_count($formula, ']') is a cheap necessary condition.
3. Prefer building table references from known-good templates (e.g. 'TableName[ColumnName]') rather than splicing raw user input.
4. Catch PhpOffice\PhpSpreadsheet\Calculation\Exception when evaluating imported formulas and map the message back to the cell coordinate.

Example fix

// before - throws "Formula Error: No closing ']' to match opening '['"
$cell->setValue('=SUM(Sales[Amount');

// after
$cell->setValue('=SUM(Sales[Amount])');

// Necessary condition: brackets must balance before calculation.
function bracketsBalanced(string $formula): bool
{
    return substr_count($formula, '[') === substr_count($formula, ']');
}

use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;

try {
    $cell->setValue('=SUM(Sales[Amount])');
    $value = $cell->calculate();
} catch (CalcException $e) {
    if (str_contains($e->getMessage(), "closing ']'")) {
        $errors[] = 'Malformed table reference in ' . $cell->getCoordinate();
    }
}

When it happens

Trigger: =SUM(Sales[Amount) or =MyTable[ in a cell; nested-bracket references with one bracket missing, e.g. =Table1[[Col]:[Val]; formulas truncated by string cutting, escaping, or CSV import; any formula where the lexer sees '[' with no later ']' (note the scanner also handles nested '[' by incrementing the required count).

Common situations: Building formulas by concatenation and forgetting the closing bracket; xlsx files generated by third-party tools that emit malformed table references; hand-edited formulas that use square brackets casually outside real table references.

Related errors

• Reaching fourth argument of an IF
• We should not be expecting a condition
• Invalid parameter passed: formula
• Token with id $id does not exist.
• Unsupported binary comparison operator

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