FORMAT_NUMBER
Last updated
Was this helpful?
18.5
Last updated
Was this helpful?
The FORMAT_NUMBER function converts a number into a string.
This function converts a into a 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.
""
No formatting at all
299792458
","
With thousandths separators
299,792,458
".3f"
With three decimals
3.141
".3"
With three significant digits
3.14
".0%"
As a rounded percentage
42%
"$.2"
As a localized currency
$99.99
".3s"
10.1kg
"de-DE"
German (Germany)
"en-CA
English (Canada)
"en-GB"
English (United Kingdom)
"en-US
English (United States)
"es-ES"
Spanish (Spain)
"es-MX
Spanish (Mexico)
"fr-CA"
French (Canada)
"fr-FR"
French (France)
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.
formatted_string (type: string)
A string created by taking the number and applying the format and locale to it(.
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.
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.
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.
Decimal Numbers
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.
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".
Without the f suffix, the format string would specify that two significant digits should be used.
For large or small numbers, restricting the number of significant digits may cause the number to be formatted using exponent (scientific) notation.
Decimal numbers that represent ratios can be formatted as percentages.
We can then embed this expression in a string to add the "m/s" unit.
To format a number as currency prefix the format string with a dollar sign ('$'), and combine the thousandths grouping (',') with a fixed precesion ('.2f').
Formatting a number as currency will take into account the locale.
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.
This can be rephrased into the following rules.
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 €.
If there is a comma, the number will be formatted with thousandths separators appropriate for the locale.
If there is a period character followed by a number it specifies the precision.
If there is no character in the last position, the default is to interpret the precision as the number of significant digits
If there is a tilde character, trailing zeros will be removed.
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.
As an unit with three significant digits
Let us now turn to a number that has many decimal places, we'll use the function to format the numerical constant π. With no formatting, there will be as many decimal places as will allow.
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 prefix.