Exceljet

Quick, clean, and to the point

How to join cell values with CONCATENATE

In this video, we'll look at the CONCATENATE function, which is an alternative to using the ampersand character to join values.

This is the same example we looked at previously, a table which contains first, middle, and last names. In column E, I'll add a formula that uses the CONCATENATE function to join these names together into a full name.

With the concatenate function, just enter each value you want to join together as a separate argument.

If I use B5 for the first argument, and D5 for the second argument, we'll get the name SusanBrown without spaces.

Just like we saw when concatenating with the ampersand character, we'll need to add any space or punctuation manually.

CONCATENATE joins things together in order, so I need to a space character as the second argument to the function. As usual, all literal text needs to be surrounded in double quotes.

To extend the formula to handle a middle initial, I need to follow the same process, adding the middle initial in column C, with space characters on either side.

As with the SUM function, you can hold down the control key to add arguments, and Excel will add in the commas for you.

You can use this trick to add extra arguments where you need spaces and then come back and convert these extra references to spaces.

Just like you saw when we used the ampersand, this will create an extra space in the name Tom Smith, since there is no middle initial.

We'll use the same approach to fix that problem.

Using IF and ISBLANK, we can test for a middle initial.

=IF(ISBLANK(C10)

If there is no middle initial, we'll concatenate just the first and last name.

If there is a middle initial, we'll join all 3 names.

So, as you can see, the CONCATENATE function and the ampersand are both valid options for joining together values.

So, which should you use?

The choice is entirely your personal preference.

I generally use the ampersand, because the formulas it creates are shorter and, to my eye at least, easy to read. However, the CONCATENATE also works well and keeps the values you are joining tidy and organized.

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns