Joining Text in Excel

Joining Text

In this section, we're gonna 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 or the ampersand concatenated as a function and you'll use the ampersand in a very simple formula. 

Exercise 1: First & Last Name

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. 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 self to add to these functions. The last cell we're going to use all 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 over to that cell. And I need to add a space. Now, when you add space inside of a function, you're adding a character. 

So what I like to say here is in Excel you can imagine there are 27 letters in the alphabet. The twenty-seventh letter is actually the space. 

And in order to put in that space between the two cell references, you'll use double-quotes. So anytime you're working with any kind of text, you'll need to use double-quotes. 

I'll put in a single double quote. And then I'll put in a space, then a second double quote. Inside those two double quotes is the space character that I want to be preserved in the concatenate function. Then I need one more comma. 

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 contain St.. Now I can press enter and that's how I get the space between Diana and St.. 

Ampersand Method

Now we'll use the ampersand method. This is a much simpler method and easy 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 gonna 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-quotes, 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 Chessie 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 in the 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 S, then I'll enter the ampersand, and then I'll put in my first double quote. And then what I want inside the double quotes is what's going to separate the region from the department. So that is going to be a space, a dash, and space. I could put all three together in a single set of double-quotes. 

Then I want another ampersand to separate the department from the space. That 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 or the ampersand to combine. Two cells or multiple cells.