How to use the 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.

Version

Excel 2013

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

Zaujímavé články...