What is the Microsoft Excel TEXT
Function?
The TEXT function is a computer program
that you run from a worksheet cell formula. It lets you take a
numeric value and convert its format using special symbols. It
is like using the Number tab on the Format cells dialog box in
Excel. You run the TEXT function by typing its name in a formula
then followed by the information it is suppose use. The TEXT
worksheet function is generally used to change values to date
formats, number currency, time...etc. Many times it is used in
combination with other functions as a
nested function. The functions that nest this function are
generally the ones that use specific text formats in their
argument list like the DATEVALUE function or the LOOKUP
functions. For example, say you had worksheet values displayed
as dates but you wanted to generate the name of the month for
the dates. Then you could use the TEXT function to accomplish
this task. The TEXT function also plays an integral part in data
mining and cleaning.
How Do You Type the TEXT
Worksheet Function in a Formula?
Whenever you type a formula in a
worksheet cell, this is called syntax or grammar. The general
TEXT function syntax has a format like this when you type it in
a worksheet cell:
= TEXT(value, format_text)
Where value, format_text are called the
function argument list. Remember, you are running a computer
program at this point so the program needs information to
operate and that is why there is an argument list. When you see
an argument list and you see square brackets [ ] around the
argument name, this means the argument is optional and you do
not have to include it when typing unless you need it. So for
the syntax above, you need to include two arguments for the TEXT
function when typing it in a worksheet cell formula in order for
it to calculate correctly. What argument values can be used are
discussed below. Remember functions expect certain things in
their argument lists, if you do not put the correct information
in the list they will generate an error when run.
TEXT Worksheet Function Argument
Definitions
 value: A numeric value, a
formula that generates a numeric value or a cell or range
reference to the desired values to convert.
 format_text: Specifies the
format to convert the value argument to. The specification
is enclosed in quotation marks, for example "m/d/yyyy" or
"#,##0.00". See the sections below for specific formatting
guidelines.
TEXT Function Formats
Guidelines for Number Formats
Display Decimal Places and
Significant Digits
To format numbers or fractions that
contain decimal points, use the following digit placeholders,
decimal points, and thousand separators for the format_text
argument.
Character 
Explanation 
0 (zero) 
Displays insignificant zeros if a number has fewer
digits than there are zeros in the format. For example,
if you type 8.9, and you want it to be displayed as
8.90, use the format #.00. 
# 
Follows the same rules as the 0 (zero). However, Excel
does not display extra zeros when the number that you
type has fewer digits on either side of the decimal than
there are # symbols in the format. For example, if the
custom format is #.##, and you type 8.9 in the cell, the
number 8.9is displayed. 
? 
Follows the same rules as the 0 (zero). However, Excel
adds a space for insignificant zeros on either side of
the decimal point so that decimal points are aligned in
the column. For example, the custom format 0.0?aligns
the decimal points for the numbers 8.9 and 88.99 in a
column. 
(period) 
Displays the decimal point in a number. 
If a number has more digits to the right
of the decimal point than there are placeholders in the format,
the number rounds to as many decimal places as there are
placeholders. If there are more digits to the left of the
decimal point than there are placeholders, the extra digits are
displayed. If the format contains only number signs (#) to the
left of the decimal point, numbers less than 1 begin with a
decimal point; for example, .47.
Original 
Modified 
Format 
1234.59 
1234.6 
"####.#" 
8.9 
8.900 
"#.000" 
0.631 
0.6 
"0.#" 
1234.568 
1234.57 
"#.0#" 
44.398
102.65
2.8

44.398
102.65
2.8
(with aligned decimals) 
"???.???" 
5.25
5.3

5 1/4
5 3/10
(with aligned fractions) 
"# ???/???" 
Display a Thousands Separator
To display a comma as a thousands
separator or to scale a number by a multiple of 1,000, include
the following separator in the number format.
Character 
Explanation 
, (comma) 
Displays the thousands separator in a number. Excel
separates thousands by commas if the format contains a
comma that is enclosed by number signs (#) or by zeros.
A comma that follows a digit placeholder scales the
number by 1,000. For example, if the format_text
argument is "#,###.0,", Excel displays the number
12,200,000 as 12,200.0. 
If a number has more digits to the right
of the decimal point than there are placeholders in the format,
the number rounds to as many decimal places as there are
placeholders. If there are more digits to the left of the
decimal point than there are placeholders, the extra digits are
displayed. If the format contains only number signs (#) to the
left of the decimal point, numbers less than 1 begin with a
decimal point; for example, .47.
Original 
Modified 
Format 
12000 
1234.6 
"#,###" 
12000 
8.900 
"#," 
12200000 
0.6 
"0.0,," 
Guidelines for Date and Time
formats
Display days, months, and years To
display numbers as date formats (such as days, months, and
years), use the following codes in the format_text argument.
Character 
Explanation 
m 
Displays the month as a number without a leading zero. 
mm 
Displays the month as a number with a leading zero when
appropriate. 
mmm 
Displays the month as an abbreviation (Jan to Dec). 
mmmm 
Displays the month as an abbreviation (Jan to Dec). 
mmmmm 
Displays the month as a single letter (J to D). 
d 
Displays the day as a number without a leading zero. 
dd 
Displays the day as a number with a leading zero when
appropriate. 
ddd 
Displays the day as an abbreviation (Sun to Sat). 
dddd 
Displays the day as a full name (Sunday to Saturday). 
yy 
Displays the year as a twodigit number. 
yyyy 
Displays the year as a fourdigit number. 
Original 
Modified 
Format 
Months 
112 
"m" 
Months 
0112 
"mm" 
Months 
JanDec 
"mmm" 
Months 
January–December 
"mmmm" 
Months 
JD 
"mmmmm" 
Days 
131 
"d" 
Days 
0131 
"dd" 
Days 
SunSat 
"ddd" 
Days 
Sunday–Saturday 
"dddd" 
Years 
00–99 
"yy" 
Years 
1900–9999 
"yyyy" 
Display hours, minutes, and seconds To
display time formats (such as hours, minutes, and seconds), use
the following codes in the format_text argument.
Character 
Explanation 
h 
Displays the hour as a number without a leading zero 
[h] 
Displays elapsed time in hours. If you are working with
a formula that returns a time in which the number of
hours exceeds 24, use a number format that resembles
[h]:mm:ss. 
hh 
Displays the hour as a number with a leading zero when
appropriate. If the format contains AM or PM, the hour
is shown based on the 12hour clock. Otherwise, the hour
is shown based on the 24hour clock. 
m 
Displays the minute as a number without a leading zero.
Note that the m or the mm code must appear immediately
after the h or hh code or immediately before the ss
code; otherwise, Excel displays the month instead of
minutes. 
[m] 
Displays elapsed time in minutes. If you are working
with a formula that returns a time in which the number
of minutes exceeds 60, use a number format that
resembles [mm]:ss. 
mm 
Displays the minute as a number with a leading zero when
appropriate. Note that the m or the mm code must appear
immediately after the h or hh code or immediately before
the ss code; otherwise, Excel displays the month instead
of minutes. 
s 
Displays the second as a number without a leading zero. 
[s] 
Displays elapsed time in seconds. If you are working
with a formula that returns a time in which the number
of seconds exceeds 60, use a number format that
resembles [ss]. 
ss 
Displays the second as a number with a leading zero when
appropriate. If you want to display fractions of a
second, use a number format that resembles h:mm:ss.00. 
AM/PM, am/pm, A/P, a/p 
Displays the hour based on a 12hour clock. Excel
displays AM, am, A, or a for times from midnight until
noon and PM, pm, P, or p for times from noon until
midnight. 
Original 
Modified 
Format 
Hours 
023 
"h" 
Hours 
0023 
"hh" 
Minutes 
059 
"m" 
Minutes 
0059 
"mm" 
Seconds 
059 
"s" 
Seconds 
0059 
"ss" 
Time 
4 AM 
"h AM/PM" 
Time 
4:36 PM 
"h:mm AM/PM" 
Time 
4:36:03 P 
"h:mm:ss A/P" 
Time 
4:36:03.75 
"h:mm:ss.00" 
Elapsed time (hours and
minutes) 
1:02 
"[h]:mm" 
Elapsed time (minutes and
seconds) 
62:16 
"[mm]:ss" 
Elapsed time (seconds and
hundredths) 
3735.80 
"[ss].00" 
Guidelines for Currency,
Percentages, and Scientific Notation Format
Include currency symbols To precede a
number with a dollar sign ($), type the dollar sign at the
beginning of the format_text argument (for example,
"$#,##0.00"). To enter one of the following currency symbols in
a number format, press NUM LOCK and use the numeric keypad to
type the ANSI code for the symbol.
Character 
Explanation 
¢ 
ALT+0162 
£ 
ALT+0163 
¥ 
ALT+0165 
Note that you must use the numeric
keypad; using the ALT key with the number keys in the top
row of the keyboard will not generate ANSI codes.
Display Percentages
To display numbers as a percentage of
100 — for example, to display .08 as 8% or 2.8 as 280% —
include the percent sign (%) in the format_text argument.
Display Scientific Notations
To display numbers in scientific
(exponential) format, use the following exponent codes in
the format_text argument.
E (E, E+, e, e+) Displays a number
in scientific (exponential) format. Excel displays a number
to the right of the "E" or "e" that corresponds to the
number of places that the decimal point was moved. For
example, if the format_text argument is "0.00E+00", Excel
displays the number 12,200,000 as 1.22E+07. If you change
the format_text argument to "#0.0E+0", Excel displays
12.2E+6.
Guidelines for Including
Text and Adding Spacing
If you include any of the following
characters in the format_text argument, they are displayed
exactly as entered.
Character 
Explanation 
$ 
Dollar sign 
+ 
Plus sign 
( 
Left parenthesis 
: 
Colon 
^ 
Caret 
' 
Apostrophe 
{ 
Left curly bracket 
< 
Lessthan sign 
= 
Equal sign 
 
Minus sign 
/ 
Slash mark 
) 
Right parenthesis 
! 
Exclamation point 
& 
Ampersand 
~ 
Tilde 
} 
Right curly bracket 
> 
Greaterthan sign 

Space character 
What Do I Type for a TEXT
Function Argument?
When typing the TEXT function in a
worksheet cell formula, you need to replace the argument list
with arguments separating each one with a comma (arg1,arg2...).
Some typical arguments you can use are:
Argument Type

Cell Formula

Example Explanation

Cell Reference

= Text( A1, "mmm" ) 
TEXT returns an abbreviated month name from cell A1
like Jun 
Range Reference 
= Text( A1:A10, "#,###.#" ) 
TEXT returns text values formatted as 1,234.00
from the range A1 to A10 and returns an array 
Cell and Range Names 
= TEXT( Current_Date, "h:mm AM/PM" ) 
TEXT returns the time like 4:33 PM from the cell
name Current_Date * 
* It is possible to name a cell or group
of cells on a worksheet and use that name in place of a range
reference or cell reference. Consult Excel help on how to name a
cell.
Things to Know About the Text
Function
 The format_text argument
cannot contain an asterisk (*).
 Using the TEXT function
converts a numeric value to formatted text, and the
result can no longer be calculated as a number. To
format a cell so that its value remains numeric,
rightclick the cell, click Format Cells, and then in
the Format Cells dialog box, on the Number tab, set the
formatting options you want. For more information about
using the Format Cells dialog box, click the Help button
(?) in the upper right corner of the dialog box.
