What does NUMBERVALUE do in Excel?

NUMBERVALUE converts a number stored as text into a real number, and lets you specify decimal and group separators.

When should I use NUMBERVALUE?

Use NUMBERVALUE when imported data contains numbers as text, especially when dots and commas are being used differently from your regional settings.

Is NUMBERVALUE available in Google Sheets?

Google Sheets has a NUMBERVALUE function with similar purpose, but this tutorial focuses on the Microsoft Excel version.

NUMBERVALUE Function in Excel | Convert Text to Numbers

Using the NUMBERVALUE Function

Quick answer: use NUMBERVALUE when a number has been imported into Excel as text and Excel cannot calculate with it properly. The function can also tell Excel which character is the decimal separator and which character is the thousands separator.

How to use the NUMBERVALUE function in Excel. The NumberValue Excel function is helpful to convert numbers that are text into numbers that can be used in a calculation. For example when converting certain European currencies to US or UK currencies you need to be able to swap the dots for commas and the commas for dots. Basically, if the decimal separator is a comma then this function is for you. Now I did another solution to this however it was a bit long winded so here goes the succinct solution.

NUMBERVALUE example

If cell A1 contains 1.234,56 as text, you can use =NUMBERVALUE(A1,",","."). This tells Excel that the comma is the decimal separator and the dot is the group separator.

When NUMBERVALUE is useful

Cleaning exported finance, sales or system reports.
Fixing imported numbers that will not add up.
Handling international number formats with different separators.
Preparing data before sorting, filtering, formulas or PivotTables.

NUMBERVALUE FAQs

Why does Excel treat my numbers as text?

This often happens when data is imported from another system or country format. Excel may not recognise the decimal separator, so it stores the value as text.

What is the difference between VALUE and NUMBERVALUE?

VALUE converts text to a number using your local settings. NUMBERVALUE gives you more control because you can specify the decimal and group separators.

Can I use NUMBERVALUE before creating a PivotTable?

Yes. It is useful before PivotTables because the values need to be real numbers if you want to sum, average or analyse them reliably.

Learn how to better use the NUMBERVALUE Excel function and a whole load more with one of our classroom based Excel Training Courses. This can be conducted and a time and place convenient to your business.