Excel NUMBERVALUE Function – How To Use

The NUMBERVALUE function converts a number in text format to numerical format using the given decimal and group separators.

The NUMBERVALUE function is very useful when downloading data in different country formats as it converts locale-specific values to locale-independent values.

NUMBERVALUE Function

Syntax

The syntax of the NUMBERVALUE function contains three arguments and is as follows:

=NUMBERVALUE(text, [decimal_separator], [group_separator])

Arguments:

'text' – This is a mandatory argument. This argument accepts the input text value that we wish to convert to a number format. It can be a direct value or a cell reference containing the input value.

'decimal_separator' – This is an optional argument. The decimal_separator accepts the character that acts as a separator between the integer part and the decimal part of the input value.

'group_separator' – This argument is also an optional one. The character that is used as a group separator in the input text value grouping the number in thousands shall be the input value of this argument.

Both separators must be enclosed in double quotes (""). For example, if the value of the text argument is '150,111.55', the decimal_separator will be a period/dot (.) separating 150111 from 55 and a comma (,) will be the group_separator.

Important Characteristics of the NUMBERVALUE Function

Some of the notable features of the NUMBERVALUE function are given below.

  • If the value of the text argument is invalid, the NUMBERVALUE function returns a #VALUE! error, and if empty, the return value will be 0.
  • If the input text value contains spaces, the function ignores them and removes them in the return value.
  • If the optional arguments are not mentioned, that is, if the value of decimal_separator and group_separator arguments are not supplied, the NUMBERVALUE function uses the current locale.
  • If more than one character is passed as a value of the decimal_separator and group_separator arguments, only the first one is considered.
  • If the value used as decimal_separator is mentioned in the text value more than once, the function gives a #VALUE! error.
  • If the group_separator is placed after the decimal_separator in the formula, the NUMBERVALUE function throws a #VALUE! error.

Another interesting attribute of the NUMBERVALUE function shows when the value of the text argument contains a percentage sign (%). In such cases, the return value of the NUMBERVALUE function is the same as when used directly with an equals sign (=). The return value of =NUMBERVALUE(10%) is 0.1, and the value of =10% is also 0.1.

Now, when multiple % are used, they are additive. Meaning when the text argument value is 10%%, the return value would be 0.001, and for 10%%% the value will be 0.00001.

Now that all the characteristics of the NUMBERVALUE function are clear, let's move on to a few examples.

Examples of NUMBERVALUE Function

Living and doing business in this era of globalization requires functions and tools to create symmetry in data for ease of data processing. Number formatting in terms of decimal and group separators varies across countries. 500.55 in the USA is written as 500,55 in Germany.

Let's check the basic functionality of the NUMBERVALUE function and how we can use it to convert text to numbers in a locale-independent format.

Example 1 – Simple Use of NUMBERVALUE Function

Here, we have taken different scenarios by varying input values to all three arguments to understand the function better.

Simple Use of NUMBERVALUE Function

The formula used will be as follows, keeping in mind that all three input values for arguments are in different columns.

=NUMBERVALUE(B3,C3,D3)

Simple Use of NUMBERVALUE Function

The first example is straightforward, where the comma is a group separator, whereas the period is a decimal separator. The second example is how most Europeans write numbers, where the comma is a decimal separator, and the period is a group separator. As evident from the left and right alignment, the value in cell B4 was in text format but is, after the NUMBERVALUE function, in numerical format.

In the third example, the input text is same as before, but we have used two input values in the decimal_separator argument. This example demonstrates the characteristic of the NUMBERVALUE function that if more than one value is passed in the decimal_separator or group_separator argument, only the first value is considered. Here, the comma is accepted as the decimal separator.

The input value for the text argument in the fourth example contains spaces, and a period is used as a decimal_separator. The NUMBERVALUE function ignores the spaces and returns the value as per the decimal and group separator.

The next two examples have the same input value for the text argument, but as the values of the decimal and group separator vary, the return values differ. When the input value is ',56' and comma is the group separator, the return value is 56, but if the same comma becomes a decimal separator, the return value is 0.56.

The NUMBERVALUE function accepts any character as the input value for decimal and group separator. In the next example, the decimal_separator is a colon, whereas the value of the group_separator argument is a dash, yet the return value is a decimal as expected.

Another characteristic that can be seen from the last example is that if the value of the text argument is empty, the return value is 0.

Hopefully, now you understand how the NUMBERVALUE function works with different input values. Let's explore other applications of the said function.

Example 2 – Calculating Sales with NUMBERVALUE Function

In this scenario, we have a global business, and we have received monthly sales data from the Italian team. Although the data is in USD, the number formatting is as per the Italian format, using a comma as a decimal separator and a period as a thousands separator.

Also, as the data came in an email, all the sales data is in text format, and we cannot process it further for calculations.

Calculating Sales with NUMBERVALUE Function

Using the NUMBERVALUE function, we can quickly convert the sales data into a number format in a locale-independent manner. This way, we can create similar formatting for all the country data, which will be easier for calculation and analysis.

The formula will be as follows, mentioning the decimal and group separator as seen in the input data.

=NUMBERVALUE(C3,",",".")

Calculating Sales with NUMBERVALUE Function

As we can see now, all the sales data is in number format, and we can also calculate the yearly sales.

Example 3 – Calculating Average with NUMBERVALUE Function

In this example, we have the notification text of the last 10 days, which provides updates on the closing stock price of Tesla. We wish to find out the average price for the last 10 days. One way is to separate the price from the text and then calculate the average price.

The more efficient manner is to use the NUMBERVALUE function combined with the RIGHT function. Then, wrapping it with the AVERAGE function to get the desired result.

Calculating Average with NUMBERVALUE Function

The RIGHT function will extract the last 6 characters of the string, which is the stock price. The function used will be as follows:

=RIGHT(B3,6)

As the RIGHT function is a text function, the extracted stock price is still in text format, therefore, cannot be used in the calculation.

Calculating Average with NUMBERVALUE Function

We can now use the NUMBERVALUE function to convert the extracted price into a numerical format. The formula used will be as follows:

=NUMBERVALUE(RIGHT(B3,6))

Calculating Average with NUMBERVALUE Function

Now that we have the stock prices in numerical format, we will use the AVERAGE function. The formula will be as follows:

=AVERAGE(C3:C12)

If we want to eliminate these steps, we can easily club all three formulas into one to get the average price. The formula used will be as follows:

=AVERAGE(NUMBERVALUE(RIGHT(B3:B12,6)))

Calculating Average with NUMBERVALUE Function

We now have the desired result with just one formula.

NUMBERVALUE Function vs VALUE Function

The VALUE function, like the NUMBERVALUE function, converts numbers from text format to numerical format. The difference is that the NUMBERVALUE function works with the specified group and decimal separator. Hence, even if Excel doesn't recognize the value as a number, the NUMBERVALUE function converts the format.

The VALUE function only accepts the input text value of numbers recognized by Microsoft Excel; else, it returns a #VALUE! error. Let's understand it better with an example.

NUMBERVALUE Function vs VALUE Function

Here the data in column B is a number in text format. The VALUE function doesn't recognize it and hence throws a #VALUE! error, whereas the NUMBERVALUE function understands it due to the decimal and group separator mentioned and returns a number in form of a decimal.

Hopefully, now you have a good grasp of the NUMBERVALUE function and how to use it to make the data more consistent and easier to calculate. Practice the NUMBERVALUE function and its application while we work on another useful Excel function for you.

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.