In addition to the very popular CONCATENATE function, you’ll find it very useful to learn how to use CONCAT in excel. It is commonly used to combine strings, combine numerical data with actual text, or create readable blocks of text for the purposes of a presentation. In this post, we’ll discuss the differences as well as how to use the ampersand (“&”) character to achieve a similar result.

CONCAT vs. CONCATENATE

CONCAT is actually a replacement for the CONCATENATE function beginning with Excel 2016. The purpose of the function is straightforward and exactly the same as that of CONCATENATE: combine 2 or more pieces of data into one cell. CONCATENATE will still remain active so as not to confuse original users of the function, but CONCAT takes all of the best parts of CONCATENATE and adds one very important feature: the ability to easily combine an ARRAY of data instead of a LIST of data.

Examples: Using CONCAT vs. CONCATENATE vs. AMPERSAND (“&”)

In the example below, we’re going to look at a very simple use case of the CONCAT family of functions; combining a first name from one column with a last name in another column. While it seems overly simple, there is a lot to unpack and many ways to do it.

Believe it or not, the results in column C above were achieved using 5 different versions of the concat/concatenate function family (we’ll worry about adding a space in the next step). For now, let’s peek under the hood:

The formulas being used in Column C are shown in column F. Let’s go through them one at a time.

1. CONCAT (A2:B2) – Selecting A and B as an array combines cells A2 and B2
2. CONCAT (A3, B3) – Same result as #1 but we insert A3 and B3 as a list (helpful for inserting NON cell references later)
3. A4&B4 – This is as simple as it gets. By using the ampersand (“&”) in between two or more cell references you want to combine, it achieves the exact same result as using CONCAT. Sometimes the ampersand is more convenient than typing out the whole function name.
4. CONCATENATE (A5:B5) – Here’s where CONCATENATE falls short. I’ve selected the array A5:B5 (the same as I had in example #1). However, this does not actually concatenate the cells together. The second cell (B5) containing “Seinfeld” SPILLS OVER to the next column, not giving us the result we want of have his name in one cell. The full spelling of this function (CONCATENATE) does not work the same as the shorter CONCAT in scenarios like this.
5. CONCATENATE (A6,B6) – Using a comma separated list of cells (array), we get the result we wanted. If you’re concatenating a dozen pieces of data together, EVERY piece of data has to be separated by a comma in order for CONCATENATE to work.

Finalizing our CONCAT and CONCATENATE Examples

The final piece of our concatenation puzzle is to add a space in between the first and last name. Because the first and last names did NOT contain a space, it’s up to us to add it using our formula.

1. CONCAT(A13,” “,B13) – In between the two cells containing first and last names, we must add a space character enclosed in quotes (” “).
2. A14&” “&B14 – This works exactly the same as our first ampersand example, but again we must add in the space character by hand.
3. CONCATENATE(A15,” “,B15) – This works exactly the same as CONCAT, comma separating our two cells and adding in a space character.
4. CONCAT(A18:C18) – Here is where CONCAT really shines. We do not need to to comma separate each individual piece of the first and last name. By selecting the array A18:C18, it’s the same as writing out the full formula CONCAT(A13,” “,B13). Imagine having 5,10,15 or even more pieces of data you have to combine into a single cell (believe it or not I have!). CONCAT will combine all of it simply by selecting the whole array. You can see other examples below

More CONCAT Examples