FORMAT_NUMBER

The FORMAT_NUMBER function converts a number into a string.

This function converts a Number into a string given formatting options and an optional locale.

Formatting options are provided as a string. A common format string is to group a number using thousandth's separators with the string ",". By default, the formatting will be applied using the current locale of the user. The optional locale parameter will override this default with the provided locale.

Common Format Strings

Supported Locales

Declaration

FORMAT_NUMBER(number, format, locale) -> formatted_string

Parameters

number (type: number)
The number to be formatted.

format (optional, type: string)
The number format. See the Examples and Discussion section for more details.

locale (optional, type: string)
String representing the locale.

Return Values

formatted_string (type: string)
A string created by taking the number and applying the format and locale to it(.

Examples

Large Numbers

Let's consider formatting a rather large number such as 299792458. With an empty string, this number will be formatted exactly as you would enter it into an expression.

FORMAT_NUMBER(299792458, "") => "299792458"

When displaying a large number to the user, it is helpful to group the digits of the number by thousandths, the format string "," will do just that. Assuming a user locale of "en-US", this will place a comma between every third digit from the right.

FORMAT_NUMBER(299792458, ",") => "299,792,458"

Note that other locales will have different separators. The German (German) locale will use periods to group thousands, while the French (France) locale will use spaces to group thousandths. Note that these examples explicitly provide the locale to be illustrative. The user's current locale will be used if no locale is provided in the third parameter. That means that if the user's system is set to German (German), the "de-de" locale will be used.

FORMAT_NUMBER(299792458, ",", "de-DE") => "299.792.458"

FORMAT_NUMBER(299792458, ",", "fr-FR") => "299 792 458"

Decimal Numbers

Let us now turn to a number that has many decimal places, we'll use the PI function to format the numerical constant π. With no formatting, there will be as many decimal places as The Number Variable Data Type will allow.

FORMAT_NUMBER(PI(), "") => 3.141592653589793

As with the thousandths grouping, different locals will represent the decimal with different characters. Both the locales "de-DE" and "fr-FR" will use a comma to separate the integer from the decimal portions of the number.

FORMAT_NUMBER(PI(), "", "de-DE") => 3,141592653589793

FORMAT_NUMBER(PI(), "", "fr-FR") => 3,141592653589793

When displaying a number to a user, a long sequence of decimal places can be distracting. The precision can be restricted in several different ways. To always display two decimal places use the format string ".2f".

FORMAT_NUMBER(PI(), ".2f") => 3.14  

Without the f suffix, the format string would specify that two significant digits should be used.

FORMAT_NUMBER(PI(), ".2") => 3.1  

For large or small numbers, restricting the number of significant digits may cause the number to be formatted using exponent (scientific) notation.

FORMAT_NUMBER(299792458, ".2") => "3e+8"

Decimal numbers that represent ratios can be formatted as percentages.

FORMAT_NUMBER(0.421, ".0%") => "42%"

The astute reader may have noticed that the number 299792458 happens to be the speed of light as measured in meters per second. By adding an s suffix the number will be formatted with the given number of significant digits and an appropriate SI prefix.

FORMAT_NUMBER(299792458, ".2s") => "300M"

We can then embed this expression in a string to add the "m/s" unit.

"{{FORMAT_NUMBER(299792458, ".2s")}}m/s" => "300Mm/s"

To format a number as currency prefix the format string with a dollar sign ('$'), and combine the thousandths grouping (',') with a fixed precesion ('.2f').

FORMAT_NUMBER(1000000, "$,.2f") => "$1,000,000.00"

Formatting a number as currency will take into account the locale.

FORMAT_NUMBER(1000000, "$,.2f", "fr-FR") => "1.000.000,00 €"

Discussion

While the introduction and examples in this article have covered several common format strings, these format strings can be combined. For example, if you would like to format a number with both thousandths and fixed precision you would use the format string ",.2f". The format string specification is as follows where terms surrounded by brackets, [ and ], are optional (note that all terms are optional). A pipe symbol, |, indicates a choice between terms. The term [s|f|e] means either the 's' character, the 'f' character, or the 'e' character.

[$][,][.precision][~][%|f|s|e|b|o|x]

This can be rephrased into the following rules.

1. If the format string is prefixed by a $ character, the number will be formatted as currency localized to the local. For example, with the "en-us" locale a number would be formatted as $1.10, while with the "fr-fr" locale it would be formatted as 1,10 €.
2. If there is a comma, the number will be formatted with thousandths separators appropriate for the locale.
3. If there is a period character followed by a number it specifies the precision.
   1. If there is no character in the last position, the default is to interpret the precision as the number of significant digits
4. If there is a tilde character, trailing zeros will be removed.
5. If there is a character in the last position it specifies the type of the format
   • % - The number will be formatted as a percentage
   • f - The number will be formatted with the specified number of decimal places. For example, 1.00, or 3.14
   • s - The number with an appropriate SI prefix. For example, the number 1000 with the format specifier .2s would be formatted as 1.0k.
   • e - The number will be formatted in exponent notation, for example, 2.99792458e+8
   • b - The number will be rounded to the nearest integer and formatted in binary. For example, 2.5 will be formatted as 11.
   • o - The number will be rounded to the nearest integer and formatted in octal. For example, 7.5 will be formatted as 10.
   • x - The number will be rounded to the nearest integer and formatted in hexadecimal. For example, 15.5 will be formatted as 10.