{"record":{"id":"aaaea6b8c8596bac","repo":"PHPOffice/PhpSpreadsheet","slug":"value-aaaea6","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":48,"sourceCode":"        }\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;\n        if ($value < $minValue) {\n            throw new CalcExp(ExcelError::VALUE());\n        }\n\n        return (int) $value;\n    }\n\n    public static function extractFloat(mixed $value): float\n    {\n        if ($value === null) {\n            $value = 0.0;\n        }\n        if (is_bool($value)) {\n            $value = (float) $value;\n        }\n        if (!is_numeric($value)) {","sourceCodeStart":30,"sourceCodeEnd":66,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/TextData/Helpers.php#L30-L66","documentation":"TextData\\Helpers::extractInt() coerces numeric arguments (character counts, start positions, decimals) for text functions. After null/bool normalization, anything non-numeric throws CalcExp with ExcelError::VALUE() ('#VALUE!'). Numeric-looking strings pass is_numeric, but genuine text like 'two' or '2.5.5' fails. Callers (LEFT/RIGHT/MID char counts, REPLACE positions, DOLLAR/FIXED decimals, SEARCH offsets) catch and return it as the cell result.","triggerScenarios":"=LEFT(A1,B1) where B1 holds text like 'two' or 'n/a'; =DOLLAR(A1,'x'); =MID(A1,\"1\",2) is fine but =MID(A1,\"abc\",2) is not; =SEARCH(A1,B1,\"off\") as the offset; calling Extract::mid('abc','x',2) or Format::DOLLAR(5,'two') directly, which throws Calculation\\Exception with message '#VALUE!'.","commonSituations":"Count/position parameters taken from user-entered or imported text cells instead of numeric ones; localized data where '2' arrives as '2,0' and fails is_numeric; optional parameter cells left with placeholder text like '-' or 'N/A'; mapping numeric options from query strings without casting.","solutions":["Point the count/position argument at a genuinely numeric cell or cast in PHP before writing the formula","Clean placeholder text: =LEFT(A1, IF(ISNUMBER(B1), B1, 1))","If you generate formulas from PHP input, (int)-cast and validate parameters first","Catch Calculation\\Exception (message '#VALUE!') when calling the helper-backed functions directly"],"exampleFix":"// before: B1 contains the text 'two'\n$sheet->getCell('C1')->setValue('=LEFT(A1,B1)'); // -> #VALUE!\n\n// after\n$sheet->getCell('C1')->setValue('=LEFT(A1, IF(ISNUMBER(B1), B1, 1))');","handlingStrategy":"validation","validationCode":"if (!is_numeric($chars) || is_string($chars) && !is_numeric($chars)) {\n    $chars = 1; // sensible default instead of '#VALUE!'\n}\n$chars = (int) $chars;","typeGuard":"function isCoercibleInt(mixed $v): bool\n{\n    return is_int($v) || is_float($v) || (is_string($v) && is_numeric($v));\n}","tryCatchPattern":"try {\n    $r = Extract::mid($text, $start, $chars);\n} catch (\\PhpOffice\\PhpSpreadsheet\\Calculation\\Exception $e) {\n    if ($e->getMessage() === '#VALUE!') { $r = $fallback; } else { throw $e; }\n}","preventionTips":["Keep count/position parameters in numeric cells; never text placeholders","Cast user input with (int) in PHP before composing formulas","Use =IF(ISNUMBER(B1), LEFT(A1,B1), LEFT(A1,1)) guards for untrusted parameters","Numeric strings pass is_numeric but real text never does - validate accordingly"],"tags":["phpspreadsheet","excel-formula","text-functions","argument-validation","value-error"],"backgroundTag":"excel-value-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}