Joining Text in Excel: Using Concatenate and Ampersand

Joining Text. In this section, we're going to take a look at joining text. This is the opposite of splitting text. So, there are two methods we can use in this situation.

We can use either CONCATENATE function or the ampersand. CONCATENATE is a function, and you'll use the ampersand in a very simple formula. Now let's take a look at our first exercise.

We want to combine Diana and Stone together in the full name cell. So, we'll start off with CONCATENATE. You'll type in equal, and best practice when it comes to typing in a function, you'll type in as little as you need to.

In this case, I'm going to type c-o-n-c-a-t-e. And I'm going to bring up CONCATENATE by pressing Tab. You can also use CONCAT function.

CONCAT is the new and improved version of CONCATENATE. But both will work equally well in this situation. Now I need to select the first cell.

It's not going to be too difficult, because the cell I need to select is actually under the word first. So, that makes it easy. I'm going to go over and select Diana.

Then, there's one more cell to add to this function. It's the last cell we're going to use. So, I'll enter a comma to separate the first from the last, and then I'll simply select the cell that's under the word last.

I'll press ENTER, and I have Diana and Stone together in one cell. But there is a problem. There is no space between Diana and Stone.

---

So, I'm going to go to that cell, and I need to add a space. Now, when you add a space inside a function, you're adding a character. So, what I like to say here is, in Excel, you can imagine there are 26 letters in the alphabet.

The 27th letter is actually the space. And to put in that space between the two cell references, you'll use double quotation marks. So, anytime you're working with any kind of text, you'll need to use double quotation marks.

I'll put in a single double quotation mark, and then I'll put in a space, then a second double quotation mark. Inside those two double quotation marks is the space character that I want to be preserved in the CONCATENATE function. And I need one more comma to separate the space from the last name.

You'll see automatically C17 is now highlighted in red, both the cell reference and the cell that contains Stone. Now, I can press ENTER, and that’s how I get the space between Diana and Stone. Now, we'll use the ampersand method.

This is a much simpler method, an easy method to use if you're just combining two things. So, I'll type in equal. Then, again, I'm going to select the first cell.

And then the ampersand is what's going to be used to separate that cell and anything else that comes after it. Immediately, what I want to come after Jesse is a space. So, in double quotation marks, I will quote the space.

And then I need to separate that space from the last name. So, I'll enter the ampersand again and then select Bennett. When I press ENTER, I now have Jesse and Bennett.

---

In the exercise we go over in the class, the exercise that we give students to take on, we want to combine the region and the department, but we want to combine them in a special way. We want there to be a space, a dash, and a space between the region and department. For this particular exercise, I'll use the ampersand method.

So, I'll type in equal. I'll select south. Then I'll enter in the ampersand.

And then I'll enter in my first double quotation mark. And then what I want inside the double quotation marks is what's going to separate the region from the department. So, that is going to be a space, a dash, and a space.

I could put all three together in a single set of double quotation marks. Then I want another ampersand to separate the department from the space-dash-space. So, I'll select sales and then I'll press ENTER.

Now I can go over to the AutoFill handle, double-click, and I've completed the exercise. All the regions are separated from the departments with a space-dash-space. That is how you can use either CONCATENATE function or the ampersand to combine two cells or multiple cells.