Exceljet

Quick, clean, and to the point

Excel NUMBERVALUE Function

Excel NUMBERVALUE function
Summary 

The Excel NUMBERVALUE function converts a number in text format to numeric value, using specified decimal and group separators. This function can be used to convert locale-specific values into locale-independent values.

Purpose 
Convert text to number with custom separators
Return value 
Numeric value
Syntax 
=NUMBERVALUE (text, [decimal_separator], [group_separator])
Arguments 
  • text - The text to convert to a number.
  • decimal_separator - [optional] The character for decimal values.
  • group_separator - [optional] The character for grouping by thousands.
Usage notes 

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 the character used to group text by thousands in the source text.

Example

For example, 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 in column E. Columns C and D 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 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
       

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.