The Excel CONCAT Function

Concat vs. Concatenate

The Excel Concat function, introduced in Excel 2016, 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:
 ABCD
1PathFilenameExtensionCombined Path & Filename
2C:\Users\Jim\Documents\notes.doc=CONCAT( A2:C2 )
3C:\Users\Jim\Documents\image1.jpg=CONCAT( A3:C3 )
 Results:
 ABCD
1PathFilenameExtensionCombined Path & Filename
2C:\Users\Jim\Documents\notes.docC:\Users\Jim\Documents\notes.doc
3C:\Users\Jim\Documents\image1.jpgC:\Users\Jim\Documents\image1.jpg

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:
 ABCD
1ForenameSurnameDate of BirthCombined Name & DOB
2JimSmith01/02/1990=CONCAT( A2, " ", B2, ", DOB: ", TEXT(C2, "mm/dd/yyyy") )
 Result:
 ABCD
1ForenameSurnameDate of BirthCombined Name & DOB
2JimSmith01/02/1990Jim 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-2016), that does not support the Concat function.