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:

The Concat function can handle up to 254 text arguments.
The result of the Concat function must not exceed 32,767 characters.
Each of the supplied text arguments can be an array of text strings/values or a single text string/value.
The Concat function was first introduced in Excel 2019 and so is not available in earlier versions of Excel. If you have an earlier version of Excel, use the Concatenate function instead.

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:

An additional space and the additional text string ", DOB: " have been used in the Concat function, to separate the values in cells A2-C2.
The Text function has been used to convert the date value in cell C2 into a string, using the date format "mm/dd/yy".
(Date and time formatting types are explained further in the Excel Custom Number Format) page.

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.

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