FORMAT_NUMBER() : format a number (decimals, leading zeros, ID)
Updated on
The FORMAT_NUMBER function formats a number for display: thousands separators, decimals, percentage or currency symbol. It always returns text, even if the result looks like a number.
The result is text, not a number. Set the column's output format to Text in the Options tab: with a Number output, the formatting is lost (leading zeros and symbols would disappear). In return, the result can no longer be used directly in a calculation.
Syntax
FORMAT_NUMBER(value, "format")
Parameters
#ParameterDescription
1valueThe number to format: an entered value, a numeric column or the result of a calculation.
2formatThe display pattern, in quotes. See the available formats below.
Available formats
Pattern
What it does
Example
"0"
Displays the whole number, with no separator or decimal. Rounded to the nearest integer.
1234567.89 → 1234568
"0,0"
Adds thousands separators. Rounded to one decimal place.
1234567.89 → 1 234 567.9
"0.00"
Forces two decimal places, with no thousands separator.
1234567 → 1234567.00
"0,0.00"
Thousands separators and two forced decimal places. The most common format for an amount.
1234567.8 → 1 234 567.80
"0.00%"
Multiplies by 100 and adds the percent sign, with two decimal places.
0.4567 → 45.67%
"0,0.00€"
Adds a currency symbol after the formatted number. Replace € with the symbol you want.
1234567 → 1 234 567.00€
"0.00E0"
Displays the number in scientific notation, with two decimal places.
1234567 → 1.23E6
Examples
Display a readable amount
The number 1234567.8 becomes 1 234 567.80€.
FORMAT_NUMBER($Total, "0,0.00€")
Display a rate as a percentage
The number 0.4567 becomes 45.67%.
FORMAT_NUMBER($Rate, "0.00%")
Create an identifier with a prefix and leading zeros
A pattern made up only of zeros pads the number on the left up to the desired length. The number 42 becomes ID0042. The column's output format must be Text for the leading zeros to be preserved.
CONCAT("ID", FORMAT_NUMBER($Number, "0000"))
The result is not displayed as expected
The leading zeros or the symbol have disappeared
The column's output format is still set to Number. Switch it to Text in the Options tab.
The result cannot be reused in a calculation
This is expected: FORMAT_NUMBER returns text. Do your calculations on the original column, and use FORMAT_NUMBER only for display.
The rounding is not what you expected
Short patterns round the number: "0" rounds to the nearest integer, "0,0" to one decimal place. Add decimal places to the pattern to keep the precision.
Go further
Identifier with prefix and leading zeros
The variants: keeping the last digits, and the RowID case.