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

$value

Error message

$value

What it means

TextData\Helpers::extractString($value, true) is the string-coercion gate for text functions (TEXT, LEFT, RIGHT, MID, REPLACE, SEARCH, etc. use it with $throwIfError). When the incoming value is a string that is itself an Excel error value ('#N/A', '#REF!', ...), it throws CalcExp whose message is that raw error string (hence the generic '$value' message). Each caller catches it and returns getMessage() as the function result, so in-sheet the error is faithfully propagated.

Source

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

    public static function convertBooleanValue(bool $value): string
    {
        if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
            return $value ? '1' : '0';
        }

        return ($value) ? Calculation::getTRUE() : Calculation::getFALSE();
    }

    /**
     * @param mixed $value String value from which to extract characters
     */
    public static function extractString(mixed $value, bool $throwIfError = false): string
    {
        if (is_bool($value)) {
            return self::convertBooleanValue($value);
        }
        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;

View on GitHub (pinned to 65b080eef4)

Solutions

1. Fix or neutralize the upstream error first: =IFERROR(VLOOKUP(...),"") before text-processing
2. Guard the text function: =IF(ISERROR(A1), "", LEFT(A1,2))
3. In PHP, pre-screen with \PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue::isError($v, true) before calling the text helpers
4. Catch Calculation\Exception when invoking the classes directly and treat getMessage() as the Excel error result

Example fix

// before: A1 = #N/A (failed lookup) -> LEFT propagates #N/A
$sheet->getCell('B1')->setValue('=LEFT(A1,2)');

// after
$sheet->getCell('B1')->setValue('=IF(ISERROR(A1), "", LEFT(A1,2))');

use PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue;

if (is_string($value) && ErrorValue::isError($value, true)) {
    return $fallback ?? $value; // decide policy instead of calling the text function
}
$text = Helpers::extractString($value, true);

function isSafeTextInput(mixed $v): bool
{
    return ! (is_string($v) && \PhpOffice\PhpSpreadsheet\Calculation\Information\ErrorValue::isError($v, true));
}

try {
    $out = Extract::left($cellValue, 2);
} catch (\PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
    $out = $e->getMessage(); // '#N/A', '#REF!', ... -> handle as error result
}

When it happens

Trigger: =LEFT(A1,2) where A1 evaluates to #N/A; =TEXT(B1,"0.00") with B1 containing #DIV/0!; =MID("#REF!",1,2) with a literal; calling Extract::left('#N/A', 2) or Format::TEXTFORMAT('#REF!', '0') directly, which surfaces Calculation\Exception with message '#N/A'/'#REF!'.

Common situations: Text cleanup functions applied to columns that still contain error results from lookups (VLOOKUP #N/A being the classic); concatenation/reporting pipelines that extract substrings from partially failing data; sheets imported with error strings stored as text.

Related errors

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

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