VALUE vs NUMBERVALUE: parsing text to numbers, and locales
Both turn text that looks like a number into a real number so you can do math. VALUE uses your spreadsheet's locale settings to interpret separators; NUMBERVALUE lets you SPECIFY the decimal and grouping characters explicitly — which is the whole point when you're cleaning data that came from a different region.
The differences at a glance
| VALUE | NUMBERVALUE | |
|---|---|---|
| Separator interpretation | Follows the app's locale settings | You pass the decimal and group separators as arguments |
| "1.234,56" (European) on a US machine | Misreads it (or errors) | NUMBERVALUE(A2,",",".") returns 1234.56 |
| Currency symbols, %, spaces | Handles common ones per locale | Strips grouping chars; % divides by 100 |
| When to reach for it | Quick cleanup of locally-formatted text | Cross-locale imports, fixed known formats |
| Availability | Universal | Excel 2013+, Google Sheets, LibreOffice (verified by execution) |
Which should you use?
- VALUE — The text already matches your machine's locale — the common case for a quick =VALUE(A2) to un-text a number, or letting arithmetic coerce it (A2*1).
- NUMBERVALUE — Imported data uses different separators than your locale (a German CSV's "1.234,56", or thousands-spaces), so you must state the decimal and grouping characters to parse it reliably regardless of machine settings.
Compatibility (from executed tests)
Both execute correctly in every version of all three apps we test. NUMBERVALUE needs Excel 2013+ but has been in Google Sheets and LibreOffice throughout our tested range. The reverse trip — number to formatted text — is TEXT; and for cleaning stray currency symbols first, see the convert-currency-text-to-a-number recipe.
Example formulas
| Text to number (local format) | =VALUE(A2) |
| Parse a European-formatted string | =NUMBERVALUE(A2,",",".") |
| Let arithmetic coerce it | =A2*1 |
Full per-version details on each function page: VALUE · NUMBERVALUE.