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

#VALUE!

Error message

#VALUE!

What it means

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.

Source

Thrown at src/PhpSpreadsheet/Calculation/TextData/Helpers.php:48

        }
        if ($throwIfError && is_string($value) && ErrorValue::isError($value, true)) {
            throw new CalcExp($value);
        }

        return StringHelper::convertToString($value);
    }

    public static function extractInt(mixed $value, int $minValue, int $gnumericNull = 0, bool $ooBoolOk = false): int
    {
        if ($value === null) {
            // usually 0, but sometimes 1 for Gnumeric
            $value = (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_GNUMERIC) ? $gnumericNull : 0;
        }
        if (is_bool($value) && ($ooBoolOk || Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE)) {
            $value = (int) $value;
        }
        if (!is_numeric($value)) {
            throw new CalcExp(ExcelError::VALUE());
        }
        $value = (int) $value;
        if ($value < $minValue) {
            throw new CalcExp(ExcelError::VALUE());
        }

        return (int) $value;
    }

    public static function extractFloat(mixed $value): float
    {
        if ($value === null) {
            $value = 0.0;
        }
        if (is_bool($value)) {
            $value = (float) $value;
        }
        if (!is_numeric($value)) {

View on GitHub (pinned to 65b080eef4)

Solutions

1. Point the count/position argument at a genuinely numeric cell or cast in PHP before writing the formula
2. Clean placeholder text: =LEFT(A1, IF(ISNUMBER(B1), B1, 1))
3. If you generate formulas from PHP input, (int)-cast and validate parameters first
4. Catch Calculation\Exception (message '#VALUE!') when calling the helper-backed functions directly

Example fix

// before: B1 contains the text 'two'
$sheet->getCell('C1')->setValue('=LEFT(A1,B1)'); // -> #VALUE!

// after
$sheet->getCell('C1')->setValue('=LEFT(A1, IF(ISNUMBER(B1), B1, 1))');

if (!is_numeric($chars) || is_string($chars) && !is_numeric($chars)) {
    $chars = 1; // sensible default instead of '#VALUE!'
}
$chars = (int) $chars;

function isCoercibleInt(mixed $v): bool
{
    return is_int($v) || is_float($v) || (is_string($v) && is_numeric($v));
}

try {
    $r = Extract::mid($text, $start, $chars);
} catch (\PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
    if ($e->getMessage() === '#VALUE!') { $r = $fallback; } else { throw $e; }
}

When it happens

Trigger: =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!'.

Common situations: 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.

Related errors

• #VALUE!
• #NUM!
• #VALUE!
• #VALUE!
• #VALUE!

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