The Excel TEXT Function

Basic Description

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

The format 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.



Text Function Examples

Example 1

The examples below show the Text function, used to apply different formatting types to various numeric values.

 Formulas:
 AB
1ValueFormatted Value
207/07/2015=TEXT(A2, "dd/mm/yyyy")
342192=TEXT(A3, "dd/mm/yyyy")
442192=TEXT(A4, "mmm dd yyyy")
518:00=TEXT(A5, "hh:mm")
60.75=TEXT(A6, "hh:mm")
736.363636=TEXT(A7, "0.00")
80.5555=TEXT(A8, "0.0%")
9567.9=TEXT(A9, "$#,##0.00")
10-5=TEXT(A10, "+ $#,##0.00;- $#,##0.00;$0.00")
115=TEXT(A11, "+ $#,##0.00;- $#,##0.00;$0.00")
120=TEXT(A12, "+ $#,##0.00;- $#,##0.00;$0.00")
 Results:
 AB
1ValueFormatted Value
207/07/201507/07/2015
34219207/07/2015
442192Jul 07 2015
518:0018:00
60.7518:00
736.36363636.36
80.555555.6%
9567.9$567.90
10-5- $5.00
115+ $5.00
120$0.00

Note that the results of the Text function, in column B of the spreadsheet above, are all text values, rather than numeric 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:
 ABC
1NameDOBName & DOB
2John SMITH03/03/1976=A2 & " " & B2  - simple concatenation of date
3  =A2 & " " & TEXT(B2,"dd/mm/yyyy")  - concatenation using Text function
 Results:
 ABC
1NameDOBName & DOB
2John SMITH03/03/1976John SMITH 27822  - uses date's underlying integer value
3  John SMITH 03/03/1976  - gives the expected result

Further information and examples of the Excel Text function can be found on the Microsoft Office website.


Excel Text Function 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.

For example, the formula

=TEXT( A2, dd/mm/yyyy )

will return the #NAME? error.

Solution:   Add quotes around the formatting definition. Eg. the above example would be corrected as:

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