How to use the TEXT function
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.
- Positive values
- Negative values
- Zero values
- 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 press with left mouse button on "Regional settings" to view your settings.
Quick navigation
Round numbers
Thousands and millions
Show leading zeros
Format positive, negative and zero values differently
Hide values
Custom groups
Fractions
Phone numbers
Indents
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.
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.
Show leading zeros
The 0 (zero) symbol allows you to show leading zeros.
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.
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.
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.
Fractions
The division symbol lets you create fractions.
Phone numbers
The 0 (zero) symbol lets you create phone numbers with leading 0's.
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.
Singular / Plural
Get excel *.xlsx file
This extensive function reference list provides detailed information including syntax, arguments, return values, and examples for many of the functions used in Excel formulas. Click on headers to sort table.
Articles with the 'TEXT' Function
The following 4 articles have formulas that contain the TEXT function.
Count a specific weekday in a date range
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they […]
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]
How to convert radians to fractions of pi
The formula in cell D3 converts the radian decimal value to fractions of pi. Formula in cell D3: =TEXT(C3/PI(),"?/?")&"*"&CHAR(182) Explaining […]
Functions in 'Text'
The TEXT function function is one of many functions in the 'Text' category.
The ASC function converts full-width (double-byte) characters to half-width (single-byte) characters. This function is for double-byte character set (DBCS) languages. […]
Use CHAR function to convert a number to the corresponding character. This is determined by your computers character set. Windows ANSI […]
The CLEAN function deletes nonprintable characters in a value, more specifically, it is designed to delete the first 32 nonprinting […]
The CODE function returns a specific number for the first character of the text argument, determined by your computers character set. […]
How to use the CONCAT function
The CONCAT function concatenates values from multiple cells. There is, however, no delimiting character or a way to ignore empty […]
How to use the CONCATENATE function
The CONCATENATE function lets you add text strings into one single text string. The function has been replaced by the […]
The EXACT function allows you to check if two values are precisely the same, it returns TRUE or FALSE. The […]
The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function […]
The FIXED function rounds a number to the specified number of decimals, formats the number in decimal format using a […]
The LEFT function extracts a specific number of characters always starting from the left. Excel Function Syntax LEFT(text, [num_chars]) Arguments […]
The LEN function returns the number of characters in a cell value. Formula in cell D3: =LEN(B3) Cell B7 contains […]
The LOWER function converts a value to lower case letters. Formula in cell C3: =LOWER(B3) Excel Function Syntax LOWER(text) Arguments […]
The MID function returns a substring from a string based on the starting position and the number of characters you want […]
The REPT function repeats a specific text a chosen number of times. Formula in cell D3: =REPT(B3,C3) Excel Function Syntax […]
The RIGHT function extracts a specific number of characters always starting from the right. Excel Function Syntax RIGHT(text,[num_chars]) Arguments text […]
How to use the SEARCH function
Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.
How to use the SUBSTITUTE function
The SUBSTITUTE function replaces a specific text string in a value. Case sensitive. Formula in cell E3: =SUBSTITUTE(B3,C3,D3) Excel Function […]
The T function returns a text value if the argument is a text value. Formula in cell C3: =T(B3) The […]
Excel function syntax TEXT(value, format_text) Converts a value to text in a specific number format. Arguments value - The string […]
How to use the TEXTJOIN function
The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]
The picture above shows you values that have multiple leading and trailing blanks, sometimes also between words. The TRIM function […]
How to use the UNICHAR function
Use the UNICHAR function to calculate a character based on a number. Excel Function Syntax UNICHAR(number) Arguments number Required. The […]
How to use the UNICODE function
Use the UNICODE function to return a Unicode number based on a character. Excel Function Syntax UNICHAR(text) Arguments text Required. […]
Converts a value to upper case letters. Formula in cell C3: =UPPER(B3) Excel Function Syntax UPPER(text) Arguments text Value to convert. Required. Excel […]
Converts a text string that represents a number to a number. This function is included for compatibility with other software. […]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form