Exceljet

Quick, clean, and to the point

How to join values with the ampersand

Often, you'll need to join together values in Excel.

A good example is when you have first, last, and middle names in separate columns and you need to join these together into one name.

This is referred to as concatenation.

In this example, we have first, middle, and last names shown separately in a table. I'll use simple concatenation to join these separate names together into a single name.

The easiest way to concatenate is to use operator for concatenation, the ampersand character. For example, I can join Susan and Brown using the formula =B5&D5.

Notice that there is no space separating the names. If you need space or punctuation, you'll need to add it to your formula as literal text.

Literal text needs to be enclosed in double quotes, and I need to add a second ampersand to formula, since I'm now joining 3 items - cell B5, a space, and cell D5 - instead of just two.

I can take the same approach to include the middle initial of the name. I just need to add two spaces as literal text.

However, notice that if I copy this formula down, I get an extra space in the name Tom Smith, since there is no middle initial.

To adapt the formula to handle an optional middle initial, I need to add some conditional logic. In this case, the IF function will work well.

To test for the middle initial, I'm going to use the ISBLANK function. If there is no middle initial ISBLANK will return true, and we'll use the formula for first and last name only. When there is a middle initial, ISBLANK will return FALSE, so we'll use the formula that combines all 3 names.

So, to recap, I'm using IF to test for a middle initial. If it's blank, we combine first and last name, and if it's not blank, we combine all three names.

In this video, we looked at how to concatenate text using the ampersand character. You can also concatenate using the CONCATENATE function. We'll look at this option in a separate video.

Course 
Core Formula
Author 
Dave Bruns