The Excel CONCATENATE Function

Basic Description

The Excel CONCATENATE function joins together a series of supplied text strings or other values, into one combined text string.

The format of the function is:

CONCATENATE( text1, [text2], ... )

where the text arguments are a set of one or more text strings or other values that you want to join together.

In current versions of Excel (Excel 2007 and later), you can supply up to 255 text arguments to the Concatenate function, but in Excel 2003, the function can only accept up to 30 text arguments.

Using the & Operator to Concatenate

Note that you can also use the & operator to concatenate in Excel.

This is described in the Excel String Concatenation page.

Concatenate Function Examples

The following spreadsheets show the Concatenate function, used to join together the strings in columns A - C of the example spreadsheet.

1ForenameSurnameDOBCombined Details
2JaneSMITH =CONCATENATE( A2, " ", B2 )
3JohnDAY =CONCATENATE( A3, " ", B3 )
4PaulJONES02/02/87=CONCATENATE( A4, " ", B4, ", DOB: ", TEXT( C4, "dd/mm/yy" ) )
1ForenameSurnameDOBCombined Details
3JohnDAY John DAY
4PaulJONES02/02/87Paul JONES, DOB: 02/02/87

Note that, in the examples above:

Further information and examples of the Excel Concatenate function are provided on the Microsoft Office website.

Trouble Shooting

Concatenate Function Common Problem - Dates & Times

One of the most common Concatenate problems encountered by Excel users is that the function result shows a date or time as a number, as shown below:

102/02/2010=CONCATENATE( "The Date is: ", A1 )
209:00 AM=CONCATENATE( "The Time is: ", A2 )
102/02/2010The Date is: 40211
209:00 AMThe Time is: 0.375
Likely Reason

Dates and times in Excel are represented internally by numbers. It is simply the formatting of a cell that causes these numbers to be displayed as dates and times. Therefore, when an Excel date or time is supplied to the Concatenate function, the result displays the underlying numeric value.


This problem can be solved by using the Excel Text function to convert the date or time value into a text string. This is shown in the example spreadsheet below:

102/02/2010=CONCATENATE( "The Date is: ", TEXT( A1, "dd/mm/yyyy" ) )
209:00 AM=CONCATENATE( "The Time is: ", TEXT( A2, "hh:mm am/pm" ) )
102/02/2010The Date is: 02/02/2010
209:00 AMThe Time is: 09:00 AM
The date and time formatting types are explained further in the Excel Custom Number Format page.