Home » Excel-Built-In-Functions » Excel-Text-Functions » Excel-Concatenate-Function

The Excel CONCATENATE Function

Function Description

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

The syntax 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.

Note that:

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

Example 1 - Concatenating Simple Text

The Concatenate function is used in column C of the following example spreadsheet, to join together the text strings in columns A and B.

 Formulas:
  A B C
1 Forename Surname Combined Details
2 Jane SMITH =CONCATENATE( A2, " ", B2 )
3 John DAY =CONCATENATE( A3, " ", B3 )
 Results:
  A B C
1 Forename Surname Combined Details
2 Jane SMITH Jane SMITH
3 John DAY John DAY

Note that, in the examples above, a space (encased in quotes) has been added to separate the forename and surname.


Example 2 - Concatenating Dates

The Concatenate function is used in cell D2 of the following example spreadsheet, to join together the text strings in cells A2 & B2 and the date in cell C2.

 Formulas:
  A B C D
1 Forename Surname DOB Combined Details
2 Paul JONES 02/02/87 =CONCATENATE( A2, " ", B2, ", DOB: ", TEXT( C2, "mm/dd/yy" ) )
 Results:
  A B C D
1 Forename Surname DOB Combined Details
2 Paul JONES 02/02/87 Paul JONES, DOB: 02/02/87

In order to concatenate the contents of cells A2, B2 and C2 of the above spreadsheet, with the date formatted correctly, we should use the Concatenate function as follows:

=CONCATENATE( A2, " ", B2, ", DOB: ", TEXT( C2, "mm/dd/yy" ) )

This formula returns the text string "Paul JONES, DOB: 02/02/87".

Note that, in the example above:


For further information and examples of the Excel Concatenate function, see the Microsoft Office website.


Concatenate Function Problem

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:

 Formulas:
  A B
1 02/02/2016 =CONCATENATE( "The Date is: ", A1 )
2 09:00 AM =CONCATENATE( "The Time is: ", A2 )
 Results:
  A B
1 02/02/2016 The Date is: 40211
2 09:00 AM The 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.

Solution

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:

 Formulas:
  A B
1 02/02/2016 =CONCATENATE( "The Date is: ", TEXT( A1, "mm/dd/yyyy" ) )
2 09:00 AM =CONCATENATE( "The Time is: ", TEXT( A2, "hh:mm am/pm" ) )
 Results:
  A B
1 02/02/2016 The Date is: 02/02/2016
2 09:00 AM The Time is: 09:00 AM

The date and time formatting types are explained further in the Excel Custom Number Format page.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions