TEXT function

The TEXT() function is an extremely powerful utility for number formatting. In essence, the TEXT() function converts a number to its textual representation. However, its capabilities extend way beyond this basic use. The TEXT() function allows you to specify exactly the way a number needs to be formatted by applying format codes.

The syntax for the TEXT() function is as follows:

• number: A numerical value
• format_string: A sequence of format codes enclosed within two quotation marks

Numbers

You can format numbers with a combination of the following codes:

• # places a digit, if there is no digit to place it places nothing: ''
• 0 places a digit, if there is no digit to place it places a zero: '0'
• ? places a digit, if there is no digit to place it places a space: ' '

Moreover, you may also use spaces and/or supporting symbols in your format.

For example:

TEXT(123, "# - ##0")
"1 - 230"

TEXT(45, "##0")
"450"

Leading zeroes

TEXT(12, "0000##")
"000012"

In fact, this also works

TEXT(12, "0000#")
"000012"

All integer digits will always be included in the final string, but you can decide how many decimal digits the result will have.

To include the decimal part of a number, you need to include a decimal separator. The decimal separator is the first period (.) or comma (,) from right to left in your format.

TEXT(12.34, "##")
"12"

TEXT(12.34, "#.##")
"12.34"

TEXT(12.34, "##,##")
"12,34"

TEXT(12.34567, "#.###")
"12.345"

TEXT(12.3, "##.#0")
"12.30"

For large numbers, it might be clearer to separate groups of thousands. You do this by including a second period (.) or a comma (,) in your format.

TEXT(1234, "#,###.##")
"1,234"

TEXT(1234, "#.###.00")
"1.234,00"

To have a thousand separators but no decimals, you can add a trailing period or comma.

TEXT(123456, "#,###.")
"123,456"

TEXT(123456, "#.###,")
"123.456"

Dates

The TEXT() function also allows you to apply formats to dates.

NOTE: The following codes are not case-sensitive.

• D displays the day as a number from 1-31
• DD displays the day as a number from 01-31
• DDD displays the day as its abbreviation Sun-Sat

• DDDD displays the day as its full name Sunday-Saturday

• M displays the month as a number from 1-12
• MM displays the month as a number from 01-12
• MMM displays the month as its abbreviation Jan-Dec
• MMMM displays the month as its full name January-December

• MMMMM displays the month as its initial J-D

• YY displays the year as a 2-digit number 00-99
• YYYY displays the year as a 4-digit number 1900-9999

A combination of these codes allows you to display the date in any format.

TEXT(1/11/2015, "MMMM D, YYYY")
"November 1, 2015"

TEXT(1/11/2015, "YY-MM-DD")
"15-11-01"

TEXT(1/11/2015, "MMM")
Nov

Percentages

The TEXT() function also allows you to format numbers as percentages. This is achieved by adding a percentage symbol (%) in the format.

TEXT(0.8943, "0.00%")
"89.43%"

Fractions

Another type of number TEXT() supports is fractions. This format allows you to display any decimal number as a fraction. This a special format that needs to be in the following formats: "# ?/?", "# 4/?", "# ?/4".

TEXT(4.340381685, "# ?/?")
"4 3/10"

TEXT(4.340381685, "# ?/2")
"4 1/2"

TEXT(4.340381685, "# ??/100")
"4 34/100"