The Excel CONCAT function joins together text from several text values, cells, and ranges. It does not provide the option to insert delimiters between the combined values like the TEXTJOIN function.
This function is new Excel 2016 and replaces the CONCATENATE function.
The syntax for this function is:
=CONCAT(text1, [text2], …)
- text1 – First text value to be joined. Can be a hard-coded text string, a cell reference, or a cell range.
- text2 – [optional] Additional text values to be join. Can be text strings, cell references, or cell ranges. Up to 253 text value arguments are allowed.
=CONCAT(“a”, “b”, “c”, A4, A6)
=CONCAT(“My name is “, A3)
- Up to 253 arguments can be specified in this function. The resulting string can’t be longer than 32,767 characters. If it is, a #VALUE error will be displayed.
- A simpler way of joining text values is with the & operator (e.g. =”one”&A2&”three”). However, this operator cannot handle cell ranges.
- If you would like to construct a sentence or name from several text values, remember to include a space enclosed in quotation marks (” “) between words to prevent them from being displayed directly after one another, e.g. =CONCAT(“Donald”,” “,”Trump”).
Examples of the Excel CONCAT Function
Join first and last names into full names
First and last names can be joined into full names with the formula below.
Concatenate all text values in an entire column
Join all text values in an entire column by using a column range expression like A:A. This can be very useful if you have a column of text values to which you regularly add new values. If you use the column range expression, there is no need to update the formula when the column grows in length.
Common errors are:
|#VALUE||Occurs when the resulting string exceeds 32,767 characters.|
Read more about the Excel CONCAT function on the official Microsoft Excel site.