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.
Correction.
It is NOT a feature in Excel 2016 one-time payment. It is a feature that was added to Excel 365, after the features were locked in 2016.
It may be Excel 2019 one-time payment.
Minor documentation error most blogs and online “reporters” make, not differentiating between features that are locked in 2016 or 2019 onetime payment, and features that were added to 365. The difference is important to those of us who avoided paying for the 365 “cash cow” because over all it is not a good value for us (ie people with just one computer).
Hi Ron,
According to Microsoft’s documentation, which I checked when writing this post, it is available in Excel 2016 and Excel 2016 and Office 365.
Mynda
Looks like stating it is available in Excel 2016 was an error in Microsoft’s documentation. I’ve reported it, so hopefully they’ll fix it soon.