NUMBERVALUE function

This article describes the formula syntax and usage of the NUMBERVALUE function in Microsoft Excel.

Description

Converts text to a number, in a locale-independent way.

Syntax

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

The NUMBERVALUE function syntax has the following arguments.

  • Text    Required. The text to convert to a number.

  • Decimal_separator    Optional. The character used to separate the integer and fractional part of the result.

  • Group_separator    Optional. The character used to separate groupings of numbers, such as thousands from hundreds and millions from thousands.

Remarks

  • If the Decimal_separator and Group_separator arguments are not specified, separators from the current locale are used.

  • If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.

  • If an empty string ("") is specified as the Text argument, the result is 0.

  • Empty spaces in the Text argument are ignored, even in the middle of the argument. For example, " 3 000 " is returned as 3000.

  • If a decimal separator is used more than once in the Text argument, NUMBERVALUE returns the #VALUE! error value.

  • If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.

  • If the group separator occurs after the decimal separator in the Text argument, NUMBERVALUE returns the #VALUE! error value.

  • If any of the arguments are not valid, NUMBERVALUE returns the #VALUE! error value.

  • If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result. Multiple percent signs are additive if they are used in the Text argument just as they are if they are used in a formula. For example, =NUMBERVALUE("9%%") returns the same result (0.0009) as the formula =9%%.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Formula

Description

Result

=NUMBERVALUE("2.500,27",",",".")

Returns 2,500.27. 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.

2500.27

=NUMBERVALUE("3.5%")

Returns 0.035. Because no optional arguments are specified, the decimal and group separators of the current locale are used. The % symbol is not shown, although the percentage is calculated.

0.035

Top of Page

Applies To: Excel 2013, Excel Online, Excel 2016 for Mac



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language