Excel CONCAT Function

The Excel CONCAT function, new in Excel 2019, replaces the legacy CONCATENATE function*. It’s a simple function that joins the values from two or more cells together into a single text string.

Syntax: =CONCAT(text1, [text2], …)

Where text1 is the contents of a cell or cells containing text, numbers, references to other cells, formulas, or text strings typed directly into the formula.

Note: If you join cells containing numbers together CONCAT will return them as a text string.

*While CONCATENATE is still available for backward compatibility, we should use CONCAT going forward in case CONCATENATE is not available in future versions of Excel.

CONCAT Function Example

An easy example is joining first names and surnames together:

The formula =CONCAT(B5," ",C5) joins the contents of cell B5, then a space surrounded by the two double quotes, then the contents of cell C5.

Of course, we can also achieve this with the ampersand operator like this:

=B5&" "&C5

Or the TEXTJOIN function which was new in Excel 2019:

=TEXTJOIN(" ",TRUE,B5:C5)

CONCAT Function vs CONCATENATE Function

While the CONCAT function is very similar to CONCATENATE, there are some differences.

CONCAT can work with ranges e.g.

Whereas CONCATENATE, when used in Excel that has dynamic arrays, will spill the result to the cells below, which defeats the purpose 😊

And when used in a version of Excel that doesn’t have dynamic arrays, CONCATENATE will only return the first result, again defeating the purpose:

Shortcut for Selecting Non-contiguous Cells

If you don’t have the CONCAT or TEXTJOIN functions yet and you want to join multiple cells together, then a quick way is to use CONCATENATE and the CTRL key to automatically insert the comma separator after each cell.

Simply hold the CTRL key down while you left click the cells as shown in the video below.

Tip: You can use the CTRL key to automatically insert commas in other functions too.