Excel function syntax TEXT( value, format_text )
Converts a value to text in a specific number format.
Arguments value - The string you want to format. You can use a cell reference here or use a text string.
format_text - Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user. Formatting basics
The TEXT function lets you easily create your own custom formats, there are four parts shown in the picture below.
You can use the semicolon to separate different settings for different values, the zero is a character symbol (among others) for numbers and the @ is a character symbol for text values.
You can ignore the semicolons if you want to format all values equal.
Formatting codes you can use . (period) - Decimal point. All digits to the left of the decimal point are shown no matter how many formatting characters.
, (comma) - The comma separates numbers into three characters. This makes large numbers easier to read.
0 - Digit symbol. Example, type 4.5 in a cell and the format_text argument is 0.00 then the output from the TEXT function is 4.50
# - Digit symbol. Does not show extra zeros. Example, type 4.5 in a cell and the format_text argument is #.## then the output from the TEXT function is 4.5
? - Digit symbol, same as # but aligns the decimal points in a column.
% - Percentage. The percentage symbol multiplies the value with 100.
E+ E- e+ e- - Scientific format.
@ - Text characters
"Text" - Characters enclosed by quotation marks shows the characters.
m - Month number without a leading zero mm - Month number with a leading zero when necessary mmm - Month as an abbreviation, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec. mmmm - Full month name. d- Day as a number without a leading zero dd - Day as a number with a leading zero when necessary ddd - Day as an abbreviation, Sun, Mon, Tue, Wed, Thu, Fri and Sat. dddd - Full day name. yy - Year as a two-digit number. yyyy - Year as a four-digit number.
h - Hour as a digit without a leading zero. [h] - Shows more than 24 hours. hh - Hour as a digit with a leading zero when needed. m - Minute without a leading zero. [m] - Shows more than 60 minutes. mm - Minute with a leading zero when necessary. s - Second without a leading zero. [s] - Shows more than 60 seconds. ss - Seconds with a leading zero when necessary. AM/PM - Shows the time of 12-hour hours. am/pm - See above. A/P - See above. a/p - See above.
Note! Check your regional settings if the format codes above don't work for you. Win 10 users can open the Control Panel and then click on "Regional settings" to view your settings. Quick navigation
Thousands and millions
Show leading zeros
Format positive, negative and zero values differently
Singular / Plural Round numbers
The number of digit symbols you use after the decimal determines which number you want to round.
You can round to thousands and millions and so on using the comma character.
Back to navigation Thousands and millions
The comma symbol scales the number by a thousand, two comma symbols scale the number by a million (1000 * 1000 = 1,000,000).
K = 1,000 and M = 1,000,000
The \ (backslash) symbol escapes the M character so Excel displays the letter M instead of converting the number into minutes.
Back to navigation Show leading zeros
The 0 (zero) symbol allows you to show leading zeros.
Back to navigation Format positive, negative and zero values differently
The semicolon character lets you specify formatting code for positive, negative, zero values and text values. The picture below shows you these settings: 0.0;-0.00;"Zero";@
The first position is formatting code for positive values, here I have chosen 0.0. All positive values are rounded to one decimal.
The second position is affecting negative values, in this case, all negative values are rounded to two decimals.
The third position changes 0 to Zero and the fourth position shows the text value.
Back to navigation Hide values
Positive values are hidden if you leave out the first position.
Negative values are hidden if you leave the second position empty.
If you leave the third position empty zeros are hidden.
Omit the fourth position to hide text values.
Back to navigation Custom groups
Group 3 is assigned to values larger than 150, Group 2 is assigned to values larger than 100, Group 1 is assigned to all other values.
Back to navigation Fractions
The division symbol lets you create fractions.
Back to navigation Phone numbers
The 0 (zero) symbol lets you create phone numbers with leading 0's.
Back to navigation Indents
Use the underscore character _ and then the character you want to use as an indent. The character is not shown.
The example below uses - (minus) as the indent character in the first position (positive numbers) to align negative and positive numbers. The first row is not aligned.
Back to navigation Singular / Plural
Back to navigation Download excel *.xlsx file TEXT function.xlsx
The following 4 articles have formulas that contain the TEXT function.
The TEXT function function is one of many functions in the 'Text' category.