PHPOffice/PhpSpreadsheet · error · PhpSpreadsheet\Calculation\Exception

#NUM!

Error message

#NUM!

What it means

#NUM! from the binary-string validator used by BIN2DEC, BIN2HEX and BIN2OCT. ConvertBinary::validateBinary() throws Calculation\Exception('#NUM!') when the (uppercased) value contains characters other than 0/1 or is longer than 10 characters - Excel binary numbers are at most 10 bits with two's-complement negatives.

Source

Thrown at src/PhpSpreadsheet/Calculation/Engineering/ConvertBinary.php:158

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

        if (strlen($value) == 10 && $value[0] === '1') { //    Two's Complement
            return str_repeat('7', 6) . strtoupper(decoct((int) bindec("11$value")));
        }
        $octVal = (string) decoct((int) bindec($value));

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

    protected static function validateBinary(string $value): string
    {
        if ((strlen($value) > preg_match_all('/[01]/', $value)) || (strlen($value) > 10)) {
            throw new Exception(ExcelError::NAN());
        }

        return $value;
    }
}

View on GitHub (pinned to 65b080eef4)

Solutions

1. Normalise the input to /^[01]{1,10}$/ before calling (strip '0b', whitespace, underscores).
2. Truncate or reject >10-bit values; if you need wider integers, convert in PHP with bindec()/gmp and write the result.
3. Check results for '#NUM!' when binary strings come from users or imports.

Example fix

// before
$dec = ConvertBinary::toDecimal('0b1010'); // '#NUM!'

// after
$bin = preg_replace('/^0b/i', '', trim($input));
$dec = preg_match('/^[01]{1,10}$/', $bin) ? ConvertBinary::toDecimal($bin) : bindec($bin);

$bin = preg_replace('/^0b/i', '', trim((string) $value));
if (!preg_match('/^[01]{1,10}$/', $bin)) {
    throw new \InvalidArgumentException('value must be 1-10 binary digits');
}
$dec = ConvertBinary::toDecimal($bin);

/** BIN2* input: 1-10 characters, digits 0/1 only. */
function isValidBinaryString(mixed $v): bool
{
    return is_string($v) && preg_match('/^[01]{1,10}$/', $v) === 1;
}

When it happens

Trigger: =BIN2DEC("102"), =BIN2DEC("10 1") (space fails), =BIN2DEC("0b101") ('B' is not 0/1), =BIN2DEC("10101010101") (11 chars); PHP calls ConvertBinary::toDecimal('1201').

Common situations: Binary strings with whitespace or '0b'/'b' prefixes from programming contexts; values copied from formatted cells; strings longer than 10 bits; hex or decimal digits passed by mistake to a BIN2* function.

Related errors

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

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