Join date and text

In this example, the goal is to join a text string to a formatted date. The challenge is that numbers lose their formatting in Excel when they are concatenated in a formula. For example, if we have the date 31-Dec-1999 in cell B5, and we join B5 to a text string and don't control the date format with a formula like this:

="The date is "&B5

The date will revert to its raw serial number format and the result will show the date as a serial number like this:

"The date is 36525".

This happens because the date December 31, 1999, is stored as the number 36525 by Excel. When date formatting is applied to a cell on the worksheet, the number is displayed as a date. However, when a cell value is concatenated to another value in a formula, it reverts to the raw number.

Using the TEXT function to control number formatting

The TEXT Function is designed to apply a number format inside a formula. The generic syntax looks like this:

TEXT(value,format_text)

Here, value is the number to format, and format_text is a special number format code to display the number in the desired format. For example, with the date December 31, 1999, in cell B5, we can use the TEXT function to display the month and year like this:

TEXT(B5,"mmmm yyyy") // returns "December 1999"

Concatenating with TEXT

Concatenation is the process of joining values to form text strings. Since the TEXT function always returns text, it works perfectly for concatenating formatted numbers to text strings. In this example, the generic formula looks like this, where cell B5 contains a date:

="The date is"&B5

As mentioned, if we don't control the formatting for the date, the result will be a number. To maintain the date formatting, we just need to replace the cell reference with the TEXT function and provide a suitable date format. In the worksheet shown, the formula in cell E4 looks like this:

="The date is "&TEXT(B5,"dddd, mmmm d, yyyy")

The result is the text string "The date is Friday, December 31, 1999".

Other date formats

You are not limited to formatting a date in the "long" format shown above. Excel's date formatting codes are flexible so you can format the same date in many different ways. The screen below shows how the same date in cell D2 can be formatted in eleven different ways by adjusting the date format codes in column B:

The formula in D5 looks like this:

="The date is "&TEXT($D$2,B5)

Inside the TEXT function, the value is provided as $D$2, locked as an absolute reference so that it won't change as the formula is copied down, and format_text is supplied as cell B5 which contains the format codes to apply. As the formula is copied down, the reference to B5 changes while the reference to D2 does not. The final result is the same date displayed in eleven different ways.*

* The results on rows 10 and 11 are the same in the example because there is no abbreviation for May. Change the date in D2 to another month to see different results.

Helpful resources

The following links provide more examples of the concepts explained above:

• Excel's custom number formats - a detailed overview on number formatting in Excel.
• How to concatenate in Excel - what concatenation is and how to use it.
• How to use number formatting in Excel - brief video.
• How to create a custom date format - brief video.