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: To convert the text value “5%” to a number with no grouping or decimal separator: To convert the string “6.000” to the number 6000, where the grouping separator in the source text is a period (.) use: 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: 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
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
