|
<< Click to Display Table of Contents >> NUMBERVALUE (convert text into a number, locale) |
Syntax:
NUMBERVALUE(Text [, DecimalSeparator] [, GroupSeparator])
Description:
This converts a text string into a number. The arguments DecimalSeparator and GroupSeparator allow you to format the text string in a locale-independent manner.
Text is the value that you want to convert into a number.
DecimalSeparator (optional) specifies which character is currently used to separate the decimal places in the text string.
GroupSeparator (optional) specifies which character is currently used in the text string to form groups of 1000s.
The result is returned as a default format: without a group separator and the zeros after the decimal separator are truncated.
Notes:
The following rules apply when using the NUMBERVALUE function:
▪If the arguments DecimalSeparator and GroupSeparator are not specified, the text string is returned according to the current locale.
▪If only the DecimalSeparator is specified, the GroupSeparator is returned according to the current locale.
▪If several characters are specified in DecimalSeparator or GroupSeparator, only the first one is used.
▪If both arguments DecimalSeparator and GroupSeparator are specified and they are the same, this results in the #VALUE! error value.
▪An empty Text string returns 0 as the result.
▪Space characters within the Text string are ignored.
▪Multiple decimal separators in the Text string result in the #VALUE! error value.
▪If the decimal separator appears before the group separator in the Text string, this results in the #VALUE! error value.
▪If the Text string cannot be understood as a number, this returns the #VALUE! error value.
▪The % sign at the end of the Text string is treated as a percentage (i.e., the number is divided by 100). Multiple % signs cause the number to be divided several times by 100.
Example:
NUMBERVALUE("42") returns 42
NUMBERVALUE("42.44") returns 42.44
NUMBERVALUE("42,44") returns 4244
NUMBERVALUE("42,44",",") returns 42.44
NUMBERVALUE("42,44",",",".") returns 42.44
NUMBERVALUE("42,000.44") returns 42000.44
NUMBERVALUE("42.000,44") returns #VALUE!
NUMBERVALUE("42.000,44",",",".") returns 42000.44
NUMBERVALUE("42,000.44",",",".") returns #VALUE!
NUMBERVALUE("42 0 0 0",",",".") returns 42000
NUMBERVALUE("42%") returns 0.42
NUMBERVALUE("42%%") returns 0.0042
Tip: Number value with unit of measure
If, for example, cell A1 contains the value "42,44 mm" and you want to obtain the value "42.44 mm" in cell B1 using the NUMBERVALUE function, this is not possible because the function expects a string that is recognizable as a number. In this case, proceed as follows:
| 1. | Enter the value "42,44" (without mm) in cell A1, and use the formula NUMBERVALUE(A1,",",".") for cell B1 for converting according to the examples above. |
| 2. | Then format cell B1 with the custom number format @" mm" (For more information, see also Working with custom number formats). |
| 3. | You will get the value "42.44 mm" in cell B1 as a result. |
Compatibility notes:
Microsoft Excel supports this function only in version 2013 or later. The function is unknown in older versions.
See also: