Function Reference: NUMBERVALUE
| Syntax | ||
| =NUMBERVALUE(text,[decimal_separator],[group_separator]) | ||
| Purpose / Description | ||
| Returns the number value of a text string, using decimal and grouping symbols other than those set in the user’s locale settings. Useful where ERP data comes from a system housed overseas and uses number separators from a different locale. | ||
| Arguments | ||
| Name | Type | Description |
| text | text string | A constant, reference or nested expression that provides a text string in which number value has been formatted using different decimal and grouping separators that the user's own locale settings. |
| [decimal_separator] | text string | An optional constant, reference or nested expression that provides a text string which contains the decimal separator to be used in interpreting the text string. Default's to the user's own locale setting if omitted. |
| [group_separator] | text string | An optional constant, reference or nested expression that provides a text string which contains the group separator to be used in interpreting the text string. Defaults to the user's own locale setting if omitted. |
| Return Values | ||
| Type | Specific Value | Description |
| number | If the type of the text argument is a text string, and the string contents can be interpreted as a number using the given decimal and group separators, that value is returned. If the type of the text argument is a number without decimals, the value is returned unchanged. |
|
| number | If the type of the text argument is a number with decimals and the local decimal is the same as the supplied group_separator, then the returned value is a "corrupted" result where the decimal character has been removed! | |
| error | #VALUE! | if the type of the text argument is a text string and it cannot be interpreted as a number, using the supplied separators. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Comment: | The need for this function is significantly reduced with the introduction of the Power Query functionality in Excel, since conversion from other locales can be handled quite simply, and in any case, with a direct database query, the formatting of the numbers for each locale becomes less relevant. This is really only for use with legacy data dumps via csv or direct worksheet links. | |
| Warning: | This function will produce invalid results if applied to numbers and the grouping separator is the same as the local decimal separator, or the decimal separator is the same as the local group separator. It should only be used with appropriate values! This occurs because first the numbers are coerced to strings, using local locale rules. Then the given group separators are removed, effectively deleting the decimal. then the result is interpreted. Be careful that this function is used only when required, it is not a panacea to solve number conversion issues. |
|
| Examples: | Open / download sample workbook: NUMBERVALUE.xlsx | |