NUMBERVALUE Function

What is the NUMBERVALUE Function?

The NUMBERVALUE function is one of Excel’s text functions that convert text values into numbers in a locale-independent way. The function was introduced in the Excel 2013 version. Thus, it is not available in earlier versions of Excel.

Excel offers multiple functions that help with formatting the data. Some of the alike to the NUMBERVALUE functions are:

1. ISNUMBER 
This function checks whether the argument refers to a number. If it does, the TRUE value is returned; otherwise, the FALSE value is returned.

2. VALUE 
This function returns the numeric value of the inputted argument.

3. TEXT 
This function allows changing the way a number appears by applying formatting. Data formatting functions become extremely important when working with huge amounts of data. They help you organize the information so that it can be further used in the analysis.

For example, auditors often receive detailed registers of the transactions from the clients. However, these detailed registers can come in different forms and formats. As a result, the auditors often need to amend the information before it can be analyzed and used in the procedures.

The NUMBERVALUE function allows financial professionals to save time analyzing the data in different formats.

NUMBERVALUE Function Access

Microsoft Excel is a great tool that consistently allows you to use your time efficiently when dealing with data. Not only it automatically perform computations, but it also lets you convert the data into a user-friendly format.

There are three methods to access the NUMBERVALUE function in Excel:

1. Formulas tab 

You can access the function with the help of the Formulas tab, which can be found on the ribbon at the top of the Excel sheet. The function is located under the Text category.

As you can see from the picture above, once you select the Formulas tab, followed by the Text category, a series of functions pop-up. Scroll down to the NUMBERVALUE function, select it, and then simply enter the arguments covered below.

2. Inputting the formula into the worksheet

When you become more familiar with the function, you can avoid using the Formulas tab by clicking on any cell in a worksheet and directly typing the following formula:

= NUMBERVALUE ((Text, [Decimal_separator], [Group_separator ])

where:

• Text (required): Text you want to convert into a number.
• Decimal_separator (optional): The character used to separate the integer and the fractional part.
• Group_separator (optional): The character used to separate groupings of numbers (for example, thousands from hundreds, etc.).

If optional arguments are omitted, the function uses separators from the current locale.

3. Utilizing the Formulas Tab with Manual Input

It is worse to mention that you will most likely use the first two methods to access the function. However, if you choose so, a third method utilizes the abovementioned methods.

To access the function using this method, select any cell in a worksheet and proceed to the Formulas tab, where you must select the Insert Function button. This will allow you to start typing the formula into the cell.

How to use the NUMBERVALUE Function in Excel?

The table below covers multiple examples of the functions used.

When you start using the function, the arguments may seem confusing, but it gets clearer with practice. Just do not hesitate to try different combinations out.

Also, remember the following points when using the NUMBERVALUE function:

1. The function uses separators from the current locale if you decide not to specify the Deceimal_separator or the Group_separator arguments when inputting the arguments.
2. When multiple characters are specified as the Deceimal_separator or the Group_separator, the function considers only the first one.
3. If the Text argument is left blank, the function will return 0.
4. Any empty spaces in the Text argument (regardless of the position) are ignored.
5. The function returns #VALUE! Error whenever the decimal separator is used more than once in the Text argument.
6. In cases when the group separator in the Text argument appears before the decimal separator, it is ignored.
7. At the same time, when the group separator in the Text argument appears after the decimal separator, the #VALUE! Error occurs.
8. When arguments are invalid, the function also returns the #VALUE! Error.
9. If there is more than one percentage sign at the end of the Text argument, the function will consider both (for example, 3%% Text arguments will be converted to 0.0003).

Practical Example of NUMBERVALUE Function

Let's say you have a list of product prices that were imported from a website, but they're formatted as text instead of numbers. Your data might look like this:

• Product A: "$25.99"
• Product B: "$19.95"
• Product C: "$39.50"

To perform calculations or analysis on these prices, you need them to be in numeric format. You can use the NUMBERVALUE function to convert these text representations into actual numbers.

1. In a blank cell, let's say cell D2, enter the formula =NUMBERVALUE("25.99") to convert the price of Product A into a numeric value.
2. Press Enter to see the result, which should be 25.99.
3. Now, copy this formula down for the rest of the products by dragging the fill handle (a small square at the bottom right corner of the cell) down from cell D2 to the cells below it.

After applying the NUMBERVALUE function, your data will look like this:

• Product A: "$25.99" (converted to 25.99)
• Product B: "$19.95" (converted to 19.95)
• Product C: "$39.50" (converted to 39.50)

Now you have the prices in numeric format, allowing you to perform calculations such as finding the total cost, average price, or comparing prices between products accurately.

Conclusion

The NUMBERVALUE function in Excel swiftly converts text values into numeric format, aiding financial professionals in data analysis. Introduced in Excel 2013, it streamlines tasks, saving time and enhancing productivity. Accessible in the Formulas tab, it's convenient for users.

Whether accessed through the Formulas tab or manually inputted, through this function, users efficiently transform text data into numeric values. NUMBERVALUE exemplifies Excel's commitment to versatile data analysis tools.

Its inclusion reflects Microsoft's dedication to empowering users with efficient solutions for managing and analyzing financial data, facilitating better decision-making processes and improving workflow efficiency.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• CONVERT Function
• CONCATENATE Function
• NOW Function
• NPV Function
• ODD Function