Part of the free Module 5: Excel Formulas and Functions · Function 66 of 105 · Full Excel course
The Excel NUMBERVALUE function converts text into a number while letting you specify which characters are used as the decimal separator and the thousands (group) separator. It solves the common problem of importing figures written in another regional format, such as “1.234,56”, which VALUE cannot read on an English-language system. NUMBERVALUE is available in Excel 2013 and later.
Syntax
=NUMBERVALUE(text, [decimal_separator], [group_separator])Arguments
| Argument | Required / Optional | Meaning |
|---|---|---|
| text | Required | The text to convert. An empty string returns 0. |
| decimal_separator | Optional | The character used as the decimal point in the text. Defaults to the system setting. Only the first character is used. |
| group_separator | Optional | The character used to group thousands in the text. Defaults to the system setting. Only the first character is used. |
How NUMBERVALUE works
NUMBERVALUE removes every group separator, treats the decimal separator as the decimal point, ignores spaces (including leading and trailing spaces inside the text) and then converts what remains. A trailing percent sign is recognised: =NUMBERVALUE("12.5%") returns 0.125, and several percent signs divide by 100 repeatedly. Group separators that appear after the decimal separator cause #VALUE!, as does any other non-numeric character. If the two separators are the same character, the function also returns #VALUE!.
Step-by-step example: convert European-format amounts
- Column A contains amounts exported from a German system, for example
1.234,56, which Excel on an English system treats as text. - In B2 enter
=NUMBERVALUE(A2,",","."). The comma is declared as the decimal separator and the full stop as the group separator. - Press Enter. The result is the numeric value 1234.56, right-aligned and ready for SUM.
- Fill down, then apply a number format to column B. Copy and paste as values if the text column is no longer needed.
Practical use cases
1. Swiss or Indian formats with spaces or apostrophes
=NUMBERVALUE(A2,".","'")Reads 1'234.50 correctly. For numbers grouped with spaces, such as 1 234,50, the space is ignored automatically so =NUMBERVALUE(A2,",") is enough.
2. Percentages stored as text
=NUMBERVALUE(A2)Converts 15% to 0.15, which is what a percentage-formatted cell expects.
3. Safe conversion of mixed columns
=IFERROR(NUMBERVALUE(A2,",","."),"Check")Flags rows that contain unexpected characters instead of stopping the whole column with errors.
4. Strip a currency code before converting
=NUMBERVALUE(SUBSTITUTE(A2,"EUR",""),",",".")Common mistakes and tips
- Separators swapped: the second argument is the decimal separator, the third is the group separator. Reversing them gives #VALUE! or a wrong number.
- Dates and times: NUMBERVALUE does not parse them. Use DATEVALUE and TIMEVALUE.
- Currency symbols: unlike VALUE, NUMBERVALUE does not accept a currency symbol. Remove it with SUBSTITUTE first.
- Negative numbers in parentheses: (123) is not recognised; use a minus sign or SUBSTITUTE the brackets.
- Older versions: Excel 2010 and earlier return #NAME?. Use VALUE with SUBSTITUTE to swap the separators instead.
NUMBERVALUE compared with VALUE
VALUE is the older, simpler function: it reads a text number in the format of the current system locale and also understands currency symbols, dates and times. NUMBERVALUE was added so that data from another locale can be converted without changing Windows regional settings or editing the text. If the text already matches your system format, both functions return the same result and VALUE is shorter to type. If the text uses foreign separators, only NUMBERVALUE works directly. A quick alternative in any version is =--A2, which coerces text to a number, but it fails on foreign separators just like VALUE. For large imports, Power Query offers the same locale-aware conversion with a Change Type Using Locale step.
Related functions
- VALUE: converts text to a number using the system locale.
- TEXT: the reverse operation, number to formatted text.
- SUBSTITUTE: removes symbols before conversion.
- FIXED: formats a number as text with chosen separators.
- See all lessons in the Excel Formulas course.
Frequently asked questions
What is the difference between NUMBERVALUE and VALUE?
VALUE uses the separators of your system locale and also reads currency, dates and times. NUMBERVALUE lets you declare the decimal and group separators, so it converts text written in another regional format.
How do I convert 1.234,56 to a number in Excel?
Use =NUMBERVALUE(A2,”,”,”.”) to declare the comma as the decimal separator and the full stop as the thousands separator.
Why does NUMBERVALUE return #VALUE!?
The text contains characters other than digits, the declared separators, spaces or a trailing percent sign, or a group separator appears after the decimal separator.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.