Documentation/Calc Functions/NUMBERVALUE

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:


 * 1) Starting from the beginning, remove all occurrences of the group separator before any decimal separator. For example, the formula   returns 1234567.89. However, the formula   reports a #VALUE! error because it contains a group separator after the decimal separator.
 * 2) 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   returns 1.234. However, the formula   reports a #VALUE! error because it contains a second decimal separator.
 * 3) Remove all whitespace characters. For example, insert the formula   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   returns 1234, with the whitespace characters removed.
 * 4) If the first character of the resulting string is a period FULL STOP (U+002E) then prepend a zero. For example, the formula   returns 0.6.
 * 5) 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   returns 0.34 and the formula   returns 0.0034.

Related LibreOffice functions:
VALUE

ODF standard:
Section 6.13.28, part 2

Equivalent Excel functions:
NUMBERVALUE