NUMBERVALUE function
NUMBERVALUE converts text to a number while letting you say which characters are the decimal and thousands separators. That makes it reliable for data from other regions, unlike VALUE.
Example
Read it as a sentence: read 1.234,5 with a comma as the decimal separator and a period as the group separator, which gives 1234.5.
A
B
C
D
1
1234.5
2
3
Syntax
NUMBERVALUE(text, [decimal_separator], [group_separator])
Arguments in [square brackets] are optional.
-
text(required) - The text holding a number.
-
decimal_separator(optional) - Character used as the decimal point in the text.
-
group_separator(optional) - Character used as thousands separator in the text.
Returns: The number.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=NUMBERVALUE("1.234,5",",",".") | 1234.5 | European separators |
=NUMBERVALUE("12%") | 0.12 | Percent |
=NUMBERVALUE("12%%") | 0.0012 | Double percent |
=NUMBERVALUE(" 1 2 ") | 12 | Spaces inside |
=NUMBERVALUE("") | 0 | Empty text |
=NUMBERVALUE("1e3") | 1000 | Scientific |
=NUMBERVALUE("abc") | #VALUE! | Bad text |
=NUMBERVALUE("1,2",",",",") | #VALUE! | Same separators |
Watch out for
- A percent sign makes the result a fraction ("5%" gives 0.05).
- Empty text returns 0.
Where it works
- Excel
- NUMBERVALUE arrived in Excel 2013. Older versions do not have it.
- Google Sheets
- No direct equivalent. Workaround: VALUE(SUBSTITUTE(SUBSTITUTE(text,".",""),",",".")).
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .