NUMBERVALUE Function
Converts a text string into a number in a locale-independent manner
Converts a text string into a number in a locale-independent manner
The NUMBERVALUE Function is categorized under TEXT functions. It will convert a text string into a number in a locale-independent manner.
The function was added in MS Excel 2013 and hence not available in earlier versions.
=NUMBERVALUE(Text,[decimal_separator],[group_separator])
The NUMBERVALUE function uses the following arguments:
Let’s see a few examples to understand how the NUMBERVALUE function works:
Suppose we input the formula NUMBERVALUE(“1.500,7″,”,”,”.”)), the function will return 1500.70. Here, the decimal separator of the text argument in the example is specified in the second argument as a comma, and the group separator is specified in the third argument as a period.
Now let’s take a second example. When we input the formula ‘=NUMBERVALUE(“10%”), the function will return 0.10. It is so as no optional arguments are specified and hence Excel took the current locale. The function calculated the percentage, although the symbol is not shown.
When the text argument ends in one or more than one percent signs (%), they will be used in the calculation of the result.
Let’s take an example. If we give the following formulas, the results will vary, as shown below:
As seen above, when the text argument included multiple percent signs, Excel considered each sign and produced the results accordingly. It returned the same result as the formula =10%% would.
Now let’s see how we can use the NUMBERVALUE function along with other Excel functions. Suppose there is a column representing a few numbers. The numbers are in centimeters so “cm” is written at the end of the number.
If we wish to get the average of the numbers, we won’t be able to do so by simply using the AVERAGE formula as “cms” is written in the cells. To get the average, we can use the formula: =AVERAGE(NUMBERVALUE(LEFT(B4:B11,3))).
If we simply press the Enter key, it will give us an error. We need to convert the original formula into an array formula. For this, we need to put in the formula in the cell and then press Ctrl-Shift-Enter to get the desired average. We can specify the number of decimals required using the decimal separator.
In column D, we used same numbers and showed the AVERAGE result to check the accuracy of the original formula.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!