# NUMBERVALUE Excel

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

N/A

Reviewed by :

Dheeraj Vaidya

Table Of Contents

## What Is NUMBERVALUE Excel Function?

The

NUMBERVALUEExcel function is an inbuiltTEXTfunction. It converts the number specified in text format into a numeric value, following a locale-independent method.Users can use the Excel

NUMBERVALUEfunction 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.

##### Table of contents

- 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.

### Syntax

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.

### How To Use NUMBERVALUE Function In Excel?

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:

**Access the function from the Excel ribbon.****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

- Choose a target cell to show the output.
- Type
**=NUMBERVALUE(**in the cell. - Enter the arguments as values, references, or formulas, and close the brackets.
- 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.

### Examples

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**.

### NUMBERVALUE Function Vs VALUE Function

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.

### Important Things To Note

- 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.

### Frequently Asked Questions (FAQs)

**1. What is the NumberValue function in Excel VBA?**

The **NumberValue** function in Excel VBA is a function that converts the text-formatted number into a numeric value based on the cited group and decimal separator. The functionality is the same as that of the **NUMBERVALUE()** in the **Text** Excel function group.

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

The aim is to convert the text values into numbers based on the specified decimal and group separators and show the output in column D.

Then, here is how we can apply the **NumberValue **method in VBA to achieve the required output in the target cells.**Step 1: **Open the sheet containing the source dataset and press **Alt **+ **F11 **to open the VBA Editor.**Step 2: **Choose the appropriate VBAProject and then **Module** under the **Insert** tab to open a new module.**Step 3: **Type the code in the Module to apply the **NUMBERVALUE() **in the target cells.**Step 4: **Choose the play icon in the menu to run the VBA code.

Finally, open the source dataset worksheet to view the **NUMBERVALUE()** output in the required target cells.

**2. What is the use of NUMBERVALUE in Excel?**

The use of **NUMBERVALUE **in Excel is in data validations and when we must change the text-formatted numbers into numeric values that we can use for mathematical evaluations.

**3. Why is NUMBERVALUE Excel not working?**

**NUMBERVALUE **Excel is not working because of the following reasons:

• The supplied argument values are invalid.

• The decimal separator appears multiple times in the supplied text value.

• The group separator appears after the decimal separator in the supplied text value.

### Download Template

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.

### Recommended Articles

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 –