Excel - Microsoft Excel Text Function

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: 

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. 

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: 

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

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. 

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

Excel Text Function Error

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

Common Error