# 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.