The Excel TEXT Function

Related Functions:

Function Description

The Excel Text function converts a supplied numeric value into text, in a user-specified format.

The syntax of the function is:

TEXT( value, format_text )

Where the function arguments are:

value	-	A numeric value, that you want to be converted into text.
format_text	-	A text string that defines the formatting that you want to be applied to the supplied value.

The format definitions that can be used in the Excel Text function are shown in the table below. These definitions have the same meaning when used in the custom style of Excel Cell Formatting.

0	-	Forces the display of a digit in its place
#	-	Display digit if it adds to the accuracy of the number (but don't display if a leading zero or a zero at the end of a decimal)
.	-	Defines the position that the decimal place takes
d	-	Day of the month or day of week d = one or two digit representation (e.g. 1, 12) dd = 2 digit representation (e.g. 01, 12) ddd = abbreviated day of week (e.g. Mon, Tue) dddd = full name of day of week (e.g. Monday, Tuesday)
m	-	Month (when used as part of a date) m = one or two digit representation (e.g. 1, 12) mm = two digit representation (e.g. 01, 12) mmm = abbreviated month name (e.g. Jan, Dec) mmmm = full name of month (e.g. January, December)
y	-	Year yy = 2-digit representation of year(e.g. 99, 08) yyyy = 4-digit representation of year(e.g. 1999, 2008)
h	-	Hour h = one or two digit representation (e.g. 1, 20) h = two digit representation (e.g. 01, 20)
m	-	Minute (when used as a part of a time) m = one or two digit representation (e.g. 1, 55) m = two digit representation (e.g. 01, 55)
s	-	Second s = one or two digit representation (e.g. 1, 45) ss = two digit representation (e.g. 01, 45)
AM/PM	-	Indicates that a time should be represented using a 12-hour clock, followed by "AM" or "PM"

Text Function Examples

Example 1

The following spreadsheet shows the Excel Text function, used to apply different formatting types to various numeric values.

Formulas:

	A	B
1	Value	Formatted Value
2	07/07/2015	=TEXT(A2, "mm/dd/yyyy")
3	42192	=TEXT(A3, "mm/dd/yyyy")
4	42192	=TEXT(A4, "mmm dd yyyy")
5	18:00	=TEXT(A5, "hh:mm")
6	0.75	=TEXT(A6, "hh:mm")
7	36.363636	=TEXT(A7, "0.00")
8	0.5555	=TEXT(A8, "0.0%")
9	567.9	=TEXT(A9, "$#,##0.00")
10	-5	=TEXT(A10, "+ $#,##0.00;- $#,##0.00;$0.00")
11	5	=TEXT(A11, "+ $#,##0.00;- $#,##0.00;$0.00")
12	0	=TEXT(A12, "+ $#,##0.00;- $#,##0.00;$0.00")

Results:

	A	B
1	Value	Formatted Value
2	07/07/2015	07/07/2015
3	42192	07/07/2015
4	42192	Jul 07 2015
5	18:00	18:00
6	0.75	18:00
7	36.363636	36.36
8	0.5555	55.6%
9	567.9	$567.90
10	-5	- $5.00
11	5	+ $5.00
12	0	$0.00

Note that the results returned from the Text function are all text values.

Example 2

One of the most common uses of the Excel Text function is to insert dates into text strings.

Without the use of the Text function, the simple concatenation of a date returns the date's underlying integer value. This is shown in the example below:

Formulas:

	A	B	C
1	Name	DOB	Name & DOB
2	John SMITH	03/03/1976	=A2 & " " & B2	- simple concatenation of date
3			=A2 & " " & TEXT(B2,"mm/dd/yyyy")	- concatenation using Text function

Results:

	A	B	C
1	Name	DOB	Name & DOB
2	John SMITH	03/03/1976	John SMITH 27822	- uses date's underlying integer value
3			John SMITH 03/03/1976	- gives the expected result

For further details and examples of the Excel Text function, see the Microsoft Office website.

Excel Text Function Common Error

Some users have problems when the Excel Text function returns the #NAME? error:

Common Error

#NAME?

This is returned from the Excel Text function, if you omit the quotation marks from around the format_text argument.

For example, the formula

=TEXT( A2, mm/dd/yyyy )

will return the #NAME? error.

Solution: Add quotes around the formatting definition.

E.g. the above example would be corrected as:

=TEXT( A2, "mm/dd/yyyy" )

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions