{"record":{"id":"8e2e931300b8a6a9","repo":"PHPOffice/PhpSpreadsheet","slug":"value-8e2e93","errorCode":null,"errorMessage":"#VALUE!","messagePattern":"#VALUE!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/TextData/Format.php","lineNumber":160,"sourceCode":"    }\n\n    /**\n     * @param mixed $value Value to check\n     */\n    private static function convertValue(mixed $value, bool $spacesMeanZero = false): mixed\n    {\n        $value = $value ?? 0;\n        if (is_bool($value)) {\n            if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {\n                $value = (int) $value;\n            } else {\n                throw new CalcExp(ExcelError::VALUE());\n            }\n        }\n        if (is_string($value)) {\n            $value = trim($value);\n            if (ErrorValue::isError($value, true)) {\n                throw new CalcExp($value);\n            }\n            if ($spacesMeanZero && $value === '') {\n                $value = 0;\n            }\n        }\n\n        return $value;\n    }\n\n    /**\n     * VALUE.\n     *\n     * @param mixed $value Value to check\n     *                         Or can be an array of values\n     *\n     * @return array<mixed>|DateTimeInterface|float|int|string A string if arguments are invalid\n     *         If an array of values is passed for the argument, then the returned result\n     *            will also be an array with matching dimensions","sourceCodeStart":142,"sourceCodeEnd":178,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/TextData/Format.php#L142-L178","documentation":"TextData\\Format::convertValue() preprocesses the argument for VALUE() and NUMBERVALUE(). After trimming, if the string is itself an Excel error value ('#REF!', '#DIV/0!', ...), it rethrows CalcExp with that exact string, propagating the upstream error into the function's result. The reported '#VALUE!' message is the special case where the input string literally was '#VALUE!'.","triggerScenarios":"=VALUE(A1) or =NUMBERVALUE(A1) where A1 contains or evaluates to an error value (for example a #VALUE! or #REF! produced by another formula); =VALUE(\"#N/A\") with a literal error string; calling Format::VALUE('#VALUE!') directly, which throws Calculation\\Exception instead of returning the string.","commonSituations":"Chained conversions where an earlier stage left error markers in text columns; imported files in which errors were saved as literal '#...' strings; ETL flows that try to normalize mixed columns with VALUE() and hit residue error text.","solutions":["Clean the source cell/region first: fix the upstream formula producing the error, or wrap it with IFERROR","Pre-check in the formula: =IF(ISERROR(A1), \"\", VALUE(A1))","Strip literal error strings in PHP before passing values in: if (ErrorValue::isError($v, true)) handle separately","When calling Format::VALUE()/NUMBERVALUE() directly, catch Calculation\\Exception and use its message as the error result"],"exampleFix":"// before: A1 holds #REF! -> VALUE propagates it\n$sheet->getCell('B1')->setValue('=VALUE(A1)');\n\n// after\n$sheet->getCell('B1')->setValue('=IF(ISERROR(A1), \"\", VALUE(A1))');","handlingStrategy":"validation","validationCode":"use PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ErrorValue;\n\nif (is_string($value) && ErrorValue::isError(trim($value), true)) {\n    return ''; // or propagate deliberately: return $value;\n}\nreturn Format::VALUE($value);","typeGuard":"function isErrorString(mixed $v): bool\n{\n    return is_string($v) && \\PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ErrorValue::isError(trim($v), true);\n}","tryCatchPattern":"try {\n    $v = Format::VALUE($input);\n} catch (\\PhpOffice\\PhpSpreadsheet\\Calculation\\Exception $e) {\n    $v = $e->getMessage(); // the propagated error string, e.g. '#REF!'\n}","preventionTips":["Clear upstream errors (IFERROR/IFNA) before VALUE()/NUMBERVALUE() stages","Sanitize imported text columns for literal '#...' strings","Treat propagated error messages as data-quality signals, not as values to re-parse"],"tags":["phpspreadsheet","excel-formula","text","value-function","error-propagation","value-error"],"backgroundTag":"error-value-propagation","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}