NUMBERVALUE Function

The NUMBERVALUE function converts a text value representing a number into a valid numeric using custom decimal and group separators. You can use NUMBERVALUE to translate numbers from a locale-specific text format into a locale-independent number. 

To perform a numeric conversion, the NUMBERVALUE function uses the custom separators you provide. The decimal_separator is the character used to separate integers from fractional values in the source text. The group_separator is the character used to group text by thousands in the source text. Both separators should be enclosed in double quotes (""). When decimal_separator and group_separator, Excel uses separators for the current locale.

Examples

To convert the text string "10,15" to the number 10.15:

=NUMBERVALUE("10,15",",") // returns 10.15

To convert the text value "5%" to a number with no grouping or decimal separator:

=NUMBERVALUE("5%") // returns 0.05

To convert the string "6.000" to the number 6000, where the grouping separator in the source text is a period (.) use:

=NUMBERVALUE("6.000",",",".") // returns 6000

In the example shown, input text is in column B and function output is in column E. Columns C and D are the decimal and group separators used in each row. The formula in E6, copied down, is:

=NUMBERVALUE(B6,C6,D6)

Notice in addition to decimal and group separators, the NUMBERVALUE automatically ignores the extra space in B9 and automatically evaluates the percentage (%) symbol in B11 by dividing by 100.

Notes

• NUMBERVALUE ignores extra space characters.
• Multiple percent symbols are additive.
• If decimal separator and/or group_separators are not provided, NUMBERVALUE uses separators from the current locale.
• NUMBERVALUE uses only the first character provided for decimal and group separators. Additional characters are discarded.
• NUMBERVALUE returns zero (0) if no text value is provided.
• NUMBERVALUE returns the #VALUE error if:
  • The decimal separator appears more than once in the source text
  • The group separator occurs after the decimal separator