LOCALVALUE

Description

The LOCALVALUE function parses a text string as a number using the current user's locale settings. The decimal and thousands separators are interpreted according to the user's language, so the same input string may produce different results for different users.

Usage

LOCALVALUE(text)

Remarks

The locale used is the preferred language set in the user's profile. The result of LOCALVALUE changes depending on which user views or runs the formula.

LOCALVALUE interprets the decimal and thousands separators based on the locale of the user running the formula:

• In English locales, . is the decimal separator and , is the thousands separator, so "1,001" is one thousand and one, and "1.001" is one point zero zero one.
• In German (and similar European) locales, , is the decimal separator and . is the thousands separator, so "1.001" is one thousand and one, and "1,001" is one point zero zero one.

Leading and trailing whitespace is ignored. A leading + sign is accepted and ignored. Negative numbers (leading -) are supported. Scientific notation (for example, "1.024E3") is accepted when formatted for the user's locale.

If the input is blank or contains only whitespace, the result is blank.

If the input cannot be parsed as a number (for example, it contains currency symbols, spaces within the value, date separators, or non-numeric text), the result is blank.

For locale-independent parsing, where . is always the decimal separator regardless of the user's language, use VALUE instead.

Locale comparison

Invalid inputs

The following inputs return blank regardless of locale:

Example

When a form collects numeric input in a text field and the user base spans multiple locales, LOCALVALUE converts each entry according to the submitting user's own locale settings:

LOCALVALUE(QUANTITY_TEXT)

When to use LOCALVALUE

Because the result of LOCALVALUE depends on the locale of the user viewing or running the formula, it can cause problems in some contexts:

• Calculated fields and validation rules: Avoid using LOCALVALUE here. Different users will see different values for the same record, which can lead to inconsistent data and failed validations depending on who is viewing the form.
• Default value formulas: This is the most appropriate use. Default value formulas are evaluated once when a user opens a form to enter data, so the result is calculated in that user's locale and then stored as a fixed value.

Choosing between LOCALVALUE and VALUE

• If your data was collected in a controlled environment where decimal notation is always English (. as decimal separator), use VALUE for predictable results across all users.
• Use LOCALVALUE in default value formulas when users enter text in their own locale and you want the parsing to match how each user formats numbers.