Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .