Home » Excel-Built-In-Functions » Excel-Text-Function Search this site: Custom Search

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:


and the arguments are described in the table below:

Value	A numeric value, that can be supplied to the function as a simple value, or as a reference to a cell containing a value
format_text	A text string that provides the formatting definition, that should be applied to the supplied value, in the returned text string

The format definitions that can be used in the Excel TEXT function are shown in the table below. Note that these definitions are also used with the 'Custom' option of Excel Cell Formatting. However, some of the options that can be used for cell formatting (eg. different text colour for positive and negative numbers) don't work in the TEXT function.

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"

Example 1

One of my most common uses of the Excel TEXT function is to incorporate dates into text strings.

For example, in the spreadsheet below, if I want to join together a name and a date of birth, using the simple & operator will not give me the result I want. This is shown in the spreadsheet below, in which cell C2 uses the simple formula


to join together the name in cell A2 and the date of birth in cell B2:


This result is returned because Excel is showing the underlying value of cell B2 which is an integer. (See the page on Excel Dates and Times for a further explanation of this).

In order to show the name, followed by the date of birth, we need to request the value of B1 to be formatted as a date. This can be done using the Excel TEXT function, as shown in the example below. The first spreadsheet shows the formula and the lower spreadsheet shows the result.

Formula

Result

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.

Function Formats

Results

Note that the results of the TEXT functions (shown in the above example) are all text values, rather than numeric values in Excel. This is seen by the alignment to the left of the cells (when the cell horizontal alignment is set to the default setting, "General").

Where did I go wrong?