The Excel CONCAT Function

Concat vs. Concatenate

The Excel Concat function, introduced in Excel 2019, replaces the old Concatenate function.

The Concat function can accept ranges of cells (as well as single cells or values) as arguments, whereas the Concatenate function can only accept single cells or values.

Concat vs. Textjoin

The Excel Concat function and the Textjoin function both join together text strings.

The difference between these two functions is that the Textjoin function can accept a delimiter to be inserted between the individual text strings, whereas the Concat function can not.

Function Description

The Excel CONCAT function joins together a series of supplied text strings into one combined text string.

The syntax of the function is:

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

where the text arguments are one or more text strings (or arrays of text strings), that you want to join together.

Notes:


Concat Function Examples

Example 1 - Concatenating Simple Text

Column D of the following spreadsheet shows two simple examples of the Concat function.

 Formulas:
  A B C D
1 Path Filename Extension Combined Path & Filename
2 C:\Users\Jim\Documents\ notes .doc =CONCAT( A2:C2 )
3 C:\Users\Jim\Documents\ image1 .jpg =CONCAT( A3:C3 )
 Results:
  A B C D
1 Path Filename Extension Combined Path & Filename
2 C:\Users\Jim\Documents\ notes .doc C:\Users\Jim\Documents\notes.doc
3 C:\Users\Jim\Documents\ image1 .jpg C:\Users\Jim\Documents\image1.jpg

The following Concat function can be used to concatenate the text strings in cells A2, B2 and C2 of the above example spreadsheet:

=CONCAT( A2:C2 )

This formula returns the text string "C:\Users\Jim\Documents\notes.doc".


Example 2 - Concatenating Dates

If you want to concatenate dates in Excel, you need to beware that dates and times are actually stored as simple numbers in Excel, and if you enter a date or a time directly into the Concat function, the underlying number will appear in the resulting text string, rather than the actual date or time.

Therefore, if you want to concatenate a date or a time, you need to use the Excel Text function to convert the date or time value into a text string first. This is shown in the example below:

 Formula:
  A B C D
1 Forename Surname Date of Birth Combined Name & DOB
2 Jim Smith 01/02/1990 =CONCAT( A2, " ", B2, ", DOB: ", TEXT(C2, "mm/dd/yyyy") )
 Result:
  A B C D
1 Forename Surname Date of Birth Combined Name & DOB
2 Jim Smith 01/02/1990 Jim Smith, DOB: 01/02/1990

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 Concat function as follows:

=CONCAT( A2, " ", B2, ", DOB: ", TEXT(C2, "mm/dd/yyyy") )

This formula returns the text string "Jim Smith, DOB: 01/02/1990".

Note that, in the example above:

See the Microsoft Office website for further examples of the Excel Concat function.


Concat Function Errors

If you get an error from the Excel Concat function, this is likely to be one of the following:

Possible Error
#VALUE! - Occurs if the result of the Concat function exceeds 32,767 characters.
#NAME? - Occurs if you are using an older version of Excel (pre-2019), that does not support the Concat function.