{"record":{"id":"3b1fac95ede1a2c3","repo":"PHPOffice/PhpSpreadsheet","slug":"value-3b1fac","errorCode":null,"errorMessage":"$value","messagePattern":"\\$value","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/TextData/Helpers.php","lineNumber":32,"sourceCode":"    public static function convertBooleanValue(bool $value): string\n    {\n        if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {\n            return $value ? '1' : '0';\n        }\n\n        return ($value) ? Calculation::getTRUE() : Calculation::getFALSE();\n    }\n\n    /**\n     * @param mixed $value String value from which to extract characters\n     */\n    public static function extractString(mixed $value, bool $throwIfError = false): string\n    {\n        if (is_bool($value)) {\n            return self::convertBooleanValue($value);\n        }\n        if ($throwIfError && is_string($value) && ErrorValue::isError($value, true)) {\n            throw new CalcExp($value);\n        }\n\n        return StringHelper::convertToString($value);\n    }\n\n    public static function extractInt(mixed $value, int $minValue, int $gnumericNull = 0, bool $ooBoolOk = false): int\n    {\n        if ($value === null) {\n            // usually 0, but sometimes 1 for Gnumeric\n            $value = (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_GNUMERIC) ? $gnumericNull : 0;\n        }\n        if (is_bool($value) && ($ooBoolOk || Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE)) {\n            $value = (int) $value;\n        }\n        if (!is_numeric($value)) {\n            throw new CalcExp(ExcelError::VALUE());\n        }\n        $value = (int) $value;","sourceCodeStart":14,"sourceCodeEnd":50,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/TextData/Helpers.php#L14-L50","documentation":"TextData\\Helpers::extractString($value, true) is the string-coercion gate for text functions (TEXT, LEFT, RIGHT, MID, REPLACE, SEARCH, etc. use it with $throwIfError). When the incoming value is a string that is itself an Excel error value ('#N/A', '#REF!', ...), it throws CalcExp whose message is that raw error string (hence the generic '$value' message). Each caller catches it and returns getMessage() as the function result, so in-sheet the error is faithfully propagated.","triggerScenarios":"=LEFT(A1,2) where A1 evaluates to #N/A; =TEXT(B1,\"0.00\") with B1 containing #DIV/0!; =MID(\"#REF!\",1,2) with a literal; calling Extract::left('#N/A', 2) or Format::TEXTFORMAT('#REF!', '0') directly, which surfaces Calculation\\Exception with message '#N/A'/'#REF!'.","commonSituations":"Text cleanup functions applied to columns that still contain error results from lookups (VLOOKUP #N/A being the classic); concatenation/reporting pipelines that extract substrings from partially failing data; sheets imported with error strings stored as text.","solutions":["Fix or neutralize the upstream error first: =IFERROR(VLOOKUP(...),\"\") before text-processing","Guard the text function: =IF(ISERROR(A1), \"\", LEFT(A1,2))","In PHP, pre-screen with \\PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ErrorValue::isError($v, true) before calling the text helpers","Catch Calculation\\Exception when invoking the classes directly and treat getMessage() as the Excel error result"],"exampleFix":"// before: A1 = #N/A (failed lookup) -> LEFT propagates #N/A\n$sheet->getCell('B1')->setValue('=LEFT(A1,2)');\n\n// after\n$sheet->getCell('B1')->setValue('=IF(ISERROR(A1), \"\", LEFT(A1,2))');","handlingStrategy":"validation","validationCode":"use PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ErrorValue;\n\nif (is_string($value) && ErrorValue::isError($value, true)) {\n    return $fallback ?? $value; // decide policy instead of calling the text function\n}\n$text = Helpers::extractString($value, true);","typeGuard":"function isSafeTextInput(mixed $v): bool\n{\n    return ! (is_string($v) && \\PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ErrorValue::isError($v, true));\n}","tryCatchPattern":"try {\n    $out = Extract::left($cellValue, 2);\n} catch (\\PhpOffice\\PhpSpreadsheet\\Calculation\\Exception $e) {\n    $out = $e->getMessage(); // '#N/A', '#REF!', ... -> handle as error result\n}","preventionTips":["Wrap lookup-heavy cells in IFNA/IFERROR before text extraction formulas","Screen cell values with ErrorValue::isError() when reading sheets in PHP","When calling TextData classes directly, always catch Calculation\\Exception and use getMessage() as the result"],"tags":["phpspreadsheet","excel-formula","text-functions","error-propagation","extract-string"],"backgroundTag":"error-value-propagation","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}