The Excel TEXT Function

Related Functions:
DOLLAR Function
FIXED Function

Basic Description

The Excel TEXT function converts a supplied 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.

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 (eg. 1, 12)
dd = 2 digit representation (eg. 01, 12)
ddd = abbreviated day of week (eg. Mon, Tue)
dddd = full name of day of week (eg. Monday, Tuesday)
m -
Month (when used as part of a date)
m = one or two digit representation (eg. 1, 12)
mm = two digit representation (eg. 01, 12)
mmm = abbreviated month name (eg. Jan, Dec)
mmmm = full name of month (eg. January, December)
y -
Year
yy = 2-digit representation of year(eg. 99, 08)
yyyy = 4-digit representation of year(eg. 1999, 2008)
h -
Hour
h = one or two digit representation (eg. 1, 20)
h = two digit representation (eg. 01, 20)
m -
Minute (when used as a part of a time)
m = one or two digit representation (eg. 1, 55)
m = two digit representation (eg. 01, 55)
s -
Second
s = one or two digit representation (eg. 1, 45)
ss = two digit representation (eg. 01, 45)
AM/PM - Indicates that a time should be represented using a 12-hour clock, followed by "AM" or "PM"


Problem with Simple Concatenation of Dates

Because an Excel date is stored as a simple number in Excel, if you attempt to join a name and date into a text string by just using the simple & operator, this will be displayed as a name followed by a number.

For example, if used in cell C2 of the spreadsheet below, the formula:

=A2 & " " & B2

gives the following result:

Example of use of the & operator with a date in Excel

Text Function Example 1

One of my 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 text string with a date gives an unexpected result (see right).

The spreadsheet below shows the Excel Text function used to produce the required merged text string.

As shown in the formula bar, the formula used is:

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

Example of use of the Excel Text Function



Text Function Example 2

The examples below show use the Text function, with a variety of specified formats. The spreadsheet on the left shows the function formats and the spreadsheet on the right shows the results.

 Formulas:
Examples of use of the Excel Text Function
 Results:
Excel Text Function Example Results

Note that the results of the Text function, in column B of the spreadsheet above, are all text values, rather than numeric values.


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" )

Return to the Excel Text Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net