NUMBERVALUE Excel

The NUMBERVALUE Excel function is an inbuilt TEXT function. It converts the number specified in text format into a numeric value, following a locale-independent method.

Users can use the Excel NUMBERVALUE function for converting text values present in the financial or statistical data imported from external sources into numbers. Thus, the function helps calculate total sales and averages, even when the inputs are text values.

For example, the following dataset lists numbers as text values, their decimal and group separators.

The aim is to convert the numbers formatted as text into numbers in a locale-independent context.

Then, we can apply the NUMBERVALUE Excel formula in the target cells to obtain the required output.

In the above NUMBERVALUE Excel formula example, the function in each target cell accepts three arguments, which are the given text value and the decimal and group separators.

Next, the NUMBERVALUE() converts the text value into a numeric value based on the specified decimal and group separators.

• The NUMBERVALUE Excel function accepts a number value in the text format and converts it into a numeric value based on a locale-independent concept.
• Users can use the Excel NUMBERVALUE function during data validations. It also helps change the financial data imported in the text format from external sources into valid numbers.
• The Excel NUMBERVALUE function accepts one mandatory argument, text, and two optional arguments, decimal_separator and group_separator.
• Using the Excel NUMBERVALUE function with other inbuilt functions, such as AVERAGE and IF, yields fruitful outcomes.

Though we can utilize the NUMBERVALUE Excel VBA function, the NUMBERVALUE() syntax to use the function directly in an Excel cell without VBA coding is as shown below:

Where,

• text: The number given in the text format we aim to convert into a number.
• decimal_separator: The character separating the integer and fractional portion of the output.
• group_separator: The character separating number groupings, such as millions from thousands.

While the first argument in the NUMBERVALUE Excel function is mandatory, the other two are optional.

Furthermore, adhering to the following points will help avoid potential errors while using the NUMBERVALUE():

• The NUMBERVALUE Excel 2010 is not available. Instead, the function is available from Excel 2013 version and above.
• The argument values can be values within double quotes, cell references, or formulas.
• If any of the supplied arguments are invalid, the function output is the #VALUE! error value.
• If we supply an empty string as the text argument value to the NUMBERVALUE Excel function, the output is zero.
• If the text argument value contains empty spaces, the NUMBERVALUE() ignores them, even if they are in the middle of the text value.
• If we do not supply the decimal_separator and group_separator argument values, the function uses the separators from the current locale.
• If we supply multiple characters as the decimal_separator and group_separator argument values, the function considers only the first character.
• If a decimal separator occurs multiple times in the text argument value, the function output is the Excel #VALUE! error value.
• If the group separator appears before the decimal separator in the text argument value, the group separator gets ignored.
• If the group separator appears after the decimal separator in the text argument value, the function output is the #VALUE! error value.
• If the supplied text argument value ends with one or multiple percent signs (‘%’), the function considers them while calculating the output.

Furthermore, multiple percent signs behave as an additive, like when we use them in an Excel formula.

While the NUMBERVALUE Excel VBA method enables us to apply the function using VBA coding, we shall see the more straightforward methods.

We can utilize the NUMBERVALUE Excel function in two ways:

1. Access the function from the Excel ribbon.
2. Enter the function into the worksheet manually.

Method #1 – Access The Function From The Excel Ribbon

Select the target cell for output - The Formulas tab - The TEXT function group down arrow - NUMBERVALUE Excel function.

The Function Arguments window will appear. Enter the arguments in the Text, Decimal_separator, and Group_separator fields.

Finally, clicking OK in the Function Arguments window will close the window, and we can view the NUMBERVALUE() output in the target cell.

Method #2 – Enter The Function Into The Worksheet Manually

1. Choose a target cell to show the output.
2. Type =NUMBERVALUE( in the cell.
3. Enter the arguments as values, references, or formulas, and close the brackets.
4. Press Enter to view the NUMBERVALUE() output as the required number.

Please note that the above methods will not work for NUMBERVALUE Excel 2010 since the function is available from Excel 2013 and above.

Check out the following NUMBERVALUE Excel function examples to use the function effectively.

Example #1

The following dataset lists numbers as text values.

The task is to convert the text values into numbers, considering the specified decimal and group separators. Assume the target cells are in column D.

Then, we can apply the NUMBERVALUE Excel function in the target cells to obtain the required numbers.

Step 1: Choose cell D2 and enter the NUMBERVALUE().

=NUMBERVALUE(A2,B2,C2)

Step 2: Press Enter to view the NUMBERVALUE Excel function output in the target cell.

Step 3: Using the Excel fill handle, update the formula in the cells D3:D9.

Step 4: Choose cell D10, enter the NUMBERVALUE Excel function, and press Enter.

=NUMBERVALUE(A10)

Step 5: Using the fill handle, enter the formula in cell D11.

In row 2, the decimal separator is a Period, and the group separator is a Comma. So, the function converts the text value as a decimal number, 1500000.75.

The text value in cell A3 is the European style of writing a number, where the decimal separator is the Comma and the group separator is the Period. So, the NUMBERVALUE() returns the locale-independent converted decimal number, 150000.75.

The text value in row 4 contains space characters and the Period as the decimal separator. Thus, the NUMBERVALUE() ignores the space characters and considers the decimal separator while converting the text to a number, 1500000.75.

The decimal_separator argument value in row 5 contains a Comma and Period. But the function considers only the Comma as the decimal separator since the function can take only one character as the decimal_separator argument value. So, the output in cell D5 is 150000.75.

While the decimal separator in row 6 is a Semi-Colon, the group separator argument is a Hyphen. Thus, the function accepts the abovementioned values as the respective decimal_separator and group_separator argument values to return the required number as 1500000.75.

In the case of rows 7 and 8, the supplied text value is the same, ",75". However, the decimal_separator and group_separator argument values differ, based on which the outputs differ. When the decimal_separator and group_separator argument values are a Period and Comma, the output is 75. On the other hand, in the vice-versa scenario, the output is 0.75.

Next, since the text argument value in row 9 is an empty text, the function output is 0.

After that, the text argument value in row 10 contains the percent sign, which gets counted when the function determines the required number. So, the resulting number is 0.75.

Furthermore, since the text argument value in row 11 contains two percent signs, the function uses them to calculate percent of the percent of 75, 0.0075.

Example #2 - Calculating Sales With NUMBERVALUE Function

The following dataset contains sales data imported from an external source in the range A1:B11.

The aim is to update the column B sales data in column C if the corresponding product name is available in column A. But, if the product name is missing in column A, the corresponding sales value must be 0.

Next, add all the column C sales values and display the output in cell C13.

Typically, we would use the Excel IF function in column C cells and the Excel SUM function in cell C13.

Step 1: Choose cell C2, enter the IF() displayed in the Formula Bar, and press Enter.

Next, using the fill handle, implement the IF() in the cells C3:C11.

Step 2: Choose cell C13, enter the SUM() displayed in the Formula Bar, and press Enter.

However, the SUM() output is 0. The reason is that the imported sales figures are in the text format, leading to the SUM() adding text values to the two zeros in the range C2:C11 and resulting in the output 0.

Thus, in such scenarios, we can use the IF NUMBERVALUE Excel functions-based formula instead of the IF().

Step 1: Choose cell C2, enter the IF() containing the NUMBERVALUE Excel function, and press Enter.

=IF(A2<>"",NUMBERVALUE(B2,",","."),0)

Next, using the fill handle, update the IF NUMBERVALUE Excel functions-based formula in the range C3:C11.

Step 2: Choose cell C13, enter the SUM(), and press Enter.

=SUM(C2:C11)

Since we apply the NUMBERVALUE Excel function in the IF(), the imported sales values that get updated in column C cells are the values converted into numbers from text.

Thus, all the column C cells now contain numbers, which the cell C13 SUM() adds successfully to return the total sales value, €261955.77.

Example #3 - Calculating Average With NUMBERVALUE Function

The following dataset contains a list of students and their test scores in Mathematics.

The task is to find the average of the specified Mathematics test scores and display the output in cell B13.

Then, we can choose cell B13, enter the Excel AVERAGE function with the range B2:B11 as the input, and press Enter.

However, the AVERAGE() output is the Excel #DIV/0! error value since the input scores are text values, as highlighted in the first image.

Thus, here is how to apply the NUMBERVALUE Excel function within the AVERAGE() in the target cell to achieve the required average test score.

Step 1: Choose cell B13 and enter the following formula

=AVERAGE(NUMBERVALUE(B2:B11))

Step 2: Press Ctrl + Shift + Enter to implement the formulas as we would execute the array formulas in Excel.

First, the NUMBERVALUE Excel function converts the test scores, in text format, into numbers. Next, the AVERAGE() determines the average of the ten test scores to return the output as 84.941.

The differences between the NUMBERVALUE Excel function and the Excel VALUE function are as follows:

• The VALUE() converts the given text string, representing a number, into a number. However, the NUMBERVALUE Excel function converts the given text-formatted number into a numeric value based on the cited decimal and group separators.
• The VALUE() input text value must be an Excel-recognized constant number, date, or time value. On the other hand, the NUMBERVALUE() works even if the given text value is not Excel-recognized.
• While the VALUE() does not remove the intra-character spaces in the supplied text value, the NUMBERVALUE() removes them.

• Ensure to supply valid argument values to the NUMBERVALUE Excel function. Otherwise, the function will return the #VALUE! error.
• The decimal separator must not appear more than once, and the group separator must not appear after the decimal separator in the supplied text argument value. Otherwise, the NUMBERVALUE() will return the #VALUE! error.
• If we do not supply the decimal_separator and group_separator argument values, the function takes them from the current locale.

This article must be helpful to understand the NUMBERVALUE Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is NUMBERVALUE Excel. We learn the NUMBERVALUE function syntax and how to use it in Excel, with examples & points to remember. You can learn more from the following articles –

• Maximum Number of Rows in Excel
• Negative Numbers in Excel
• Accounting Number Format in Excel