Text & Cleaning
Parse European Comma-Decimal & Thousands-Separated Text (NUMBERVALUE Pattern)
Convert locale-specific numeric strings such as "1.250,75", "4 820,50", and "12,5%" into true numbers regardless of system regional settings using SUBSTITUTE, TRIM, and VALUE.
Why plain VALUE() fails on cross-locale ERP and bank CSV exports
Plain VALUE(text) depends on the host operating system’s locale: pasting continental European amounts like "1.250,75" (dot thousands separator, comma decimal separator) or French non-breaking-space amounts "4 820,50" into a US-locale workbook triggers #VALUE! or misreads 1.250 as 1.25.
Reproducing Excel’s locale-independent NUMBERVALUE(text, decimal_separator, group_separator) in three ordered steps — (1) strip regular and non-breaking spaces plus the group separator, (2) replace the source decimal separator with ".", and (3) divide trailing "%" strings by 100 — parses numbers identically on every machine.
| Raw Imported Text | Decimal Sep | Group Sep | Normalized String | Numeric Output |
|---|---|---|---|---|
| "1.250,75" | "," | "." | "1250.75" | 1250.75 |
| "4 820,50" | "," | " " | "4820.50" | 4820.5 |
| "12,5%" | "," | "." | "12.5%" -> / 100 | 0.125 |
| "-980,25" | "," | "." | "-980.25" | -980.25 |
Locale-Independent NUMBERVALUE Parser (With ISNUMBER Pre-Guard)
Preserves already-numeric cells untouched via IF(ISNUMBER(B2), B2, ...) so "." is never stripped from parsed decimals, then cleans whitespace, CHAR(160), group separator D2, decimal separator C2, and trailing "%" on text strings.
=IF(ISNUMBER(B2), B2, LET(cleanWs, SUBSTITUTE(SUBSTITUTE(TRIM(B2), CHAR(160), ""), " ", ""), noGrp, IF(LEN(D2) = 0, cleanWs, SUBSTITUTE(cleanWs, D2, "")), stdTxt, SUBSTITUTE(noGrp, C2, "."), IF(RIGHT(stdTxt, 1) = "%", VALUE(LEFT(stdTxt, LEN(stdTxt) - 1)) / 100, VALUE(stdTxt))))Safe Fallback Wrapper for Blank or Text Header Cells
Wraps the parser in IFERROR(..., 0) so blank lines or currency footnotes do not break column SUMs.
=IFERROR(IF(ISNUMBER(B2), B2, LET(cleanWs, SUBSTITUTE(SUBSTITUTE(TRIM(B2), CHAR(160), ""), " ", ""), noGrp, IF(LEN(D2) = 0, cleanWs, SUBSTITUTE(cleanWs, D2, "")), stdTxt, SUBSTITUTE(noGrp, C2, "."), IF(RIGHT(stdTxt, 1) = "%", VALUE(LEFT(stdTxt, LEN(stdTxt) - 1)) / 100, VALUE(stdTxt)))), 0)Try the example
Use the original values or change them to test your understanding.