NUMBERVALUE function
Quick answer
NUMBERVALUE converts text that looks like a number into a real number, using the decimal and thousands separators you name rather than your computer's settings.
- Syntax
=NUMBERVALUE(text, [decimal_separator], [group_separator])- Most common use
=NUMBERVALUE(A2,",",".")returns 1234.56 (Comma for decimals, period for thousands: 1234.56)
Use it on data exported from another country, such as "1.234,56", or on numbers that arrive as text with spaces or a trailing percent sign.
- Available in
- Excel 2013 and later
- Category
- Text functions
- Returns
- A number; #VALUE! if the text can't be read with those separators.
Syntax
=NUMBERVALUE(text, [decimal_separator], [group_separator])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to convert. |
decimal_separator | Optional | Optional: the character used for decimals in the text; a period if omitted. |
group_separator | Optional | Optional: the character used for thousands; a comma if omitted. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B2 | =NUMBERVALUE(A2,",",".") | 1234.56 | Comma for decimals, period for thousands: 1234.56 |
| B3 | =NUMBERVALUE(A3) | 12500 | Spaces are ignored: 12500 |
| B4 | =NUMBERVALUE(A4,",",".") | 0.075 | A trailing % divides by 100: 0.075 |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Imported | Result |
| 1.234,56 | 1234.56 |
| 12 500 | 12500 |
| 7,5% | 0.075 |
Practice NUMBERVALUE
Graded instantly, then re-checked on changed data.
Convert European prices
B2:B6 came from a German system as text, with a comma for decimals and a period for thousands. In C2, convert each one to a number. Fill down to C6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #VALUE! | The decimal separator appears twice, or a thousands separator appears after the decimal. Check which character the source used for each. |
| The number is 1,000 times off | The separators are swapped. For "1.234,56" the decimal is "," and the thousands separator is ".". |
| Currency symbols | NUMBERVALUE doesn't strip symbols like $ or €. Remove them first with SUBSTITUTE. |
Tips
- Spaces anywhere in the text are ignored, so numbers like "12 500" convert directly.
Questions
How do I convert European number formats in Excel?
=NUMBERVALUE(A2,",",".") reads "1.234,56" as 1234.56, whatever your own regional settings.
What is the difference between VALUE and NUMBERVALUE?
VALUE uses your computer's regional settings; NUMBERVALUE uses the separators you give it, so it works the same everywhere.
Updated Checked by running: every formula on this page was run by our formula engine on .