Master the art of combining text in Excel using the Concatenate function and ampersand method. Improve your spreadsheet skills by learning how to format joined text professionally and efficiently.
Key Insights
- Utilize either Excel's Concatenate (or the newer Concat) function or the ampersand (&) method to merge text from multiple cells effectively.
- Insert spaces or special characters, like dashes, within quotations to format combined text properly, as demonstrated with examples such as merging "Diana" and "Stone" into "Diana Stone."
- Practice combining data using real-world exercises, such as joining region and department information with specific formatting (e.g., "South - Sales"), enhancing your data management capabilities.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
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 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.
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 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 27th 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. 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 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 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 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 a 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 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 or the ampersand to combine two cells or multiple cells.