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

#VALUE!

#VALUE!

Error message

#VALUE!

What it means

#VALUE! from the decimal validator used by DEC2BIN, DEC2HEX and DEC2OCT. ConvertDecimal::validateDecimal() only permits characters in [-0123456789.] - anything else (comma, space, 'e', currency symbols) makes strlen exceed the preg_match_all count and throws Calculation\Exception('#VALUE!'), returned as the result string.

Source

Thrown at src/PhpSpreadsheet/Calculation/Engineering/ConvertDecimal.php:208

            $places = self::validatePlaces($places);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $value = (int) floor((float) $value);
        if ($value > self::LARGEST_OCTAL_IN_DECIMAL || $value < self::SMALLEST_OCTAL_IN_DECIMAL) {
            return ExcelError::NAN();
        }
        $r = decoct($value);
        $r = substr($r, -10);

        return self::nbrConversionFormat($r, $places);
    }

    protected static function validateDecimal(string $value): string
    {
        if (strlen($value) > preg_match_all('/[-0123456789.]/', $value)) {
            throw new Exception(ExcelError::VALUE());
        }

        return $value;
    }
}

View on GitHub (pinned to 65b080eef4)

Solutions

1. Pass plain numeric strings or ints: strip thousands separators (str_replace(',', '', $v)) and trim whitespace.
2. Cast genuinely numeric values with (int) or (float) before calling - is_numeric values survive validateValue, but the cleanest fix is a real int.
3. For scientific notation, cast through (int) (float) $value first.
4. Check results for '#VALUE!' when decimal strings come from imports.

Example fix

// before
$bin = ConvertDecimal::toBinary(number_format(1000)); // '1,000' -> '#VALUE!'

// after
$bin = ConvertDecimal::toBinary((int) str_replace([',', ' '], '', '1,000'));

$dec = (is_numeric($value)) ? (string) (int) $value : preg_replace('/[^0-9.+-]/', '', $value);
if (!preg_match('/^[-+]?\d+(\.\d+)?$/', $dec)) {
    throw new \InvalidArgumentException('decimal value contains invalid characters');
}
$result = ConvertDecimal::toBinary($dec);

/** DEC2* input: only digits, minus and dot are legal. */
function isValidDecimalString(mixed $v): bool
{
    return is_string($v) && preg_match('/^[-+]?\d+(\.\d+)?$/', $v) === 1;
}

When it happens

Trigger: =DEC2BIN("1,000") (thousands separator), =DEC2HEX("1e5") ('e' is rejected), =DEC2BIN(" 42") (leading space), =DEC2BIN("12a"); PHP calls ConvertDecimal::toBinary(number_format(1000)) where number_format produced '1,000'.

Common situations: Numbers formatted with number_format() or locale thousands separators before conversion; scientific-notation strings from JSON/float serialization; values pasted from text with stray characters.

Related errors

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

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