In a Nutshell
The CONCAT function combines the text from multiple ranges and/or strings.
Syntax
=CONCAT(text1, [text2], …)
The arguments (text1, text2, …) are up to 253 items to be joined.
Each argument listed in the brackets can either be a string, or an array of strings, such as a range of cells.
The first one is required; the rest are optional.
Notes
The CONCAT function is a worksheet function so it can be entered as part of a formula in a worksheet cell (see examples below).
CONCAT replaced the CONCATENATE function in Excel 2019, but the CONCATENATE function will stay available for compatibility with earlier versions of Excel.
Examples
Let us have a look at the following examples (Download the example workbook to follow along):
Example 1 – Joining two adjacent cells
The formula below joins the text strings in cells B3 and C3, into one text string (in cell D3).
=CONCAT(B3, C3)
As these cells are next to each other, the cells can alternatively be selected as a range instead:
=CONCAT(B3:C3)
Example 2 – Joining two cells with a separating character in-between
In the first example, the data in columns B and C were combined, but with no delimiters. You will notice that First Name and Last Name were combined into one word, i.e., with no spaces in between.
In order to add a space between the first and last name on each row, we can take the contents in column B, join it with a “space” character, and then join it with the contents of column C. These three arguments are all separated by commas:
=CONCAT(B3, ” “, C3)
This can be achieved more elegantly with the TEXTJOIN function.
For each row in the example below, we want to combine all of the values from column B to column F.
In row 3 we will use the following formula to join all the cells from B3 through to F3:
=CONCAT(B3:F3):
As we can see in the image above, all of the individual bits including codes and seperators, were combined to form a concatenated string in column G.
In case you missed it, here is a link to the example workbook.
Errors
- CONCAT produces a #VALUE! error if the resulting string exceeds 32,767 characters.
Excel Versions Supported
Excel 2019 onwards