Documentation/Calc Functions/NUMBERVALUE
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
NUMBERVALUE
Category:
Text
Summary:
Converts the string representation of a number into a locale-independent number format. The input number may be in a locale-dependent or other bespoke format. By default, the output number is formatted as a valid IEEE (Institute of Electrical and Electronics Engineers) floating point type, which utilizes the full stop character (Unicode U+002E) as the decimal separator and does not include group separators.
Syntax:
NUMBERVALUE(Text[; Decimal Separator[; Group Separator]])
Returns:
Returns a real number corresponding to the number supplied as a text string. The default is for the returned number to be formatted as a valid IEEE floating point type, but this can be overridden by changing the formatting applied to the formula cell.
Arguments:
Text is a text string (in quotation marks), or a reference to a cell containing a text string, that contains the number to be converted.
Decimal Separator is a single character, or a reference to a cell containing a single character, that specifies the character used as the decimal separator in Text. This argument can be omitted if Text does not include any decimal or group separators.
Group Separator is a text string, or a reference to a cell containing a text string, that specifies the character(s) used as the group separator in Text. This argument can be omitted if Text does not include any group separators.
- If Text does not contain a valid number, then NUMBERVALUE reports a #VALUE! error.
- If Decimal Separator does not specify a single character, then NUMBERVALUE reports an invalid argument error (Err:502).
- If the character specified by Decimal Separator is included within Group Separator, then NUMBERVALUE reports an invalid argument error (Err:502).
Additional details:
ODF 1.2 states that the supplied string should be transformed according to five rules:
- Starting from the beginning, remove all occurrences of the group separator before any decimal separator. For example, the formula
=NUMBERVALUE("1,234,567.89"; "."; ",")returns 1234567.89. However, the formula=NUMBERVALUE("1,234.567,89"; "."; ",")reports a #VALUE! error because it contains a group separator after the decimal separator. - Starting from the beginning, replace the first occurrence in the text of the decimal separator character with the FULL STOP (U+002E) character. For example, the formula
=NUMBERVALUE("1#234"; "#")returns 1.234. However, the formula=NUMBERVALUE("1#23#4"; "#")reports a #VALUE! error because it contains a second decimal separator. - Remove all whitespace characters. For example, insert the formula
="1" & UNICHAR(32) & "2" & UNICHAR(9) & "3" & UNICHAR(10) & "4" & UNICHAR(13)into cell A1. This string contains the digits 1, 2, 3, and 4 interspersed with various Unicode whitespace characters (that is, space (U+0020), tab (U+0009), line feed (U+000A), and carriage return (U+000D)). Then the formula=NUMBERVALUE($A$1)returns 1234, with the whitespace characters removed. - If the first character of the resulting string is a period FULL STOP (U+002E) then prepend a zero. For example, the formula
=NUMBERVALUE(".6", ".")returns 0.6. - If the string ends in one or more instances of PERCENT SIGN (U+0025), remove the percent sign(s). If percent signs are removed in this way, the value of the returned number is divided by 100 for each percent sign removed. For example, the formula
=NUMBERVALUE("34%")returns 0.34 and the formula=NUMBERVALUE("34%%")returns 0.0034.
Examples:
| Formula | Description | Returns |
|---|---|---|
| =NUMBERVALUE("1.234.567,89"; ","; ".") | Here the function removes the two group separators and changes the decimal separator from a comma to a full stop. | 1234567.89 |
| =NUMBERVALUE(D1; D2; D3) where cells D1, D2, and D3 contain the strings "1.234.567,89", ",", and "." respectively. | Here the function again removes the two group separators and changes the decimal separator from a comma to a full stop. | 1234567.89 |
| =NUMBERVALUE("123·4"; "·") | Here the function changes the decimal separator from a "·" to a full stop. No group separator is used in the supplied number and so the Group Separator argument is omitted. | 123.4 |
| =NUMBERVALUE("123e12") | Here the function returns 1.23E+14. No decimal or group separators are used in the supplied number and so the Decimal Separator and Group Separator arguments are omitted. | 1.23E+14 |
| =NUMBERVALUE("1#!234#!567"; "."; "#!") | Here the function returns 1234567. Note that in this case the group separator is specified as a two-character string. | 1234567 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
NUMBERVALUE since v.2013.