{"record":{"id":"ed1b963977167780","repo":"PHPOffice/PhpSpreadsheet","slug":"formula-error-no-closing-to-match-opening","errorCode":null,"errorMessage":"Formula Error: No closing ']' to match opening '['","messagePattern":"Formula Error: No closing '\\]' to match opening '\\['","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php","lineNumber":71,"sourceCode":"\n    public function __construct(string $structuredReference)\n    {\n        $this->value = $structuredReference;\n    }\n\n    /** @param string[] $matches */\n    public static function fromParser(string $formula, int $index, array $matches): self\n    {\n        $val = $matches[0];\n\n        $srCount = substr_count($val, self::OPEN_BRACE)\n            - substr_count($val, self::CLOSE_BRACE);\n        while ($srCount > 0) {\n            $srIndex = strlen($val);\n            $srStringRemainder = substr($formula, $index + $srIndex);\n            $closingPos = strpos($srStringRemainder, self::CLOSE_BRACE);\n            if ($closingPos === false) {\n                throw new Exception(\"Formula Error: No closing ']' to match opening '['\");\n            }\n            $srStringRemainder = substr($srStringRemainder, 0, $closingPos + 1);\n            --$srCount;\n            if (str_contains($srStringRemainder, self::OPEN_BRACE)) {\n                ++$srCount;\n            }\n            $val .= $srStringRemainder;\n        }\n\n        return new self($val);\n    }\n\n    /**\n     * @throws Exception\n     * @throws \\PhpOffice\\PhpSpreadsheet\\Exception\n     */\n    public function parse(Cell $cell): string\n    {","sourceCodeStart":53,"sourceCodeEnd":89,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/Engine/Operands/StructuredReference.php#L53-L89","documentation":"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]).","triggerScenarios":"=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).","commonSituations":"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.","solutions":["Balance the brackets in the structured reference: every '[' needs its ']'.","Pre-check formula strings before calculation: substr_count($formula, '[') === substr_count($formula, ']') is a cheap necessary condition.","Prefer building table references from known-good templates (e.g. 'TableName[ColumnName]') rather than splicing raw user input.","Catch PhpOffice\\PhpSpreadsheet\\Calculation\\Exception when evaluating imported formulas and map the message back to the cell coordinate."],"exampleFix":"// before - throws \"Formula Error: No closing ']' to match opening '['\"\n$cell->setValue('=SUM(Sales[Amount');\n\n// after\n$cell->setValue('=SUM(Sales[Amount])');","handlingStrategy":"try-catch","validationCode":"// Necessary condition: brackets must balance before calculation.\nfunction bracketsBalanced(string $formula): bool\n{\n    return substr_count($formula, '[') === substr_count($formula, ']');\n}","typeGuard":null,"tryCatchPattern":"use PhpOffice\\PhpSpreadsheet\\Calculation\\Exception as CalcException;\n\ntry {\n    $cell->setValue('=SUM(Sales[Amount])');\n    $value = $cell->calculate();\n} catch (CalcException $e) {\n    if (str_contains($e->getMessage(), \"closing ']'\")) {\n        $errors[] = 'Malformed table reference in ' . $cell->getCoordinate();\n    }\n}","preventionTips":["Build structured references from templates: \"{$table}[{$column}]\".","Run a bracket-balance pre-check on formulas assembled from user input.","Remember nested specifiers like [[Col]:[Val]] need one ']' per '['."],"tags":["phpspreadsheet","formula-parser","structured-reference","excel-table","unbalanced-brackets"],"backgroundTag":"formula-parser-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}