How to Join Pieces of Text

There are two ways you can join text together. Well three I guess, if you count the manual way, which is to literally type out your joint text. But that’s just not very practical. Imagine you have 5,000 first names in one column, and 5,000 last names in the next column. Now imagine your boss wants to see 5,000 full names in one column instead. How could you possibly put 5,000 full names together?

I’ll tell you about two formulas you can use to accomplish this. The first method uses Concatenate, and the second method uses Ampersand.

Let me define those for you first, since those are some big words Concatenate simply means “to join together.” And Ampersand is simply the fancy word for this symbol “&”. Now let’s discussed how they’re used in the Excel world.


The CONCATENATE Function

In Excel, the CONCATENATE function allows you to join to pieces of text together. To use this function, type “=CONCATENATE().” Then inside the parentheses, select the pieces of text you want to join, separating them with commas.

For example, let’s use a hypothetical set of first, middle, and last names. Download it to follow along.

To combine the first and last names, you would type “=CONCATENATE(A2,C2)” in D2.

Is this what you got?

That’s not very nice looking is it? What if we want a space between the first and last name? Just change your formula to “=CONCATENATE(A2,” “,C2).” The ” ” in the formula denotes a text space. And yes, you need to actually type out the quotes.

There, that’s better!

Now let’s try combining first, middle, and last names. Type “=CONCATENATE(A2,” “,B2,” “,C2)” in E2.

This is what it should look like now.

Now you just have to copy your formulas down to the rest of the rows, and you’re set. Pretty straightforward right? Let’s try the Ampersand method.


The Ampersand Symbol

In Excel, the ampersand symbol (&) is used for the same purpose as CONCATENATE. In other words, you can join text with CONCATENATE, and you can also join text with the ampersand symbol. With ampersand, you’re not actually using a function, but rather, you’re just selecting the pieces of text you want to join and sticking a “&” in between them.

Let’s go through that same example, but use the “&” symbol instead. In D2, type “=A2&C2.”

Did I trick you again? Does it look like this?

You have to include that ” ” again! Let’s start over. Type “=A2″&” “&C2.”

Looks good right?

Try combining first, middle, and last names now. You’ve probably already guessed, the formula is “=A2&” “&B2&” “&C2.”

Also pretty easy, right?

Now just copy the formulas down. Here’s what it would look like with the formulas filled all the way down.

So if they serve the same purpose, which one should you use? I would say it’s a matter of personal preference. I tend to use ampersand more often than CONCATENATE, simply because “&” is a lot shorter to type than “CONCATENATE” But if I have to join a lot of cells together, I find myself using CONCATENATE. Typing all of those &’s gets really old after a while!

So there you have it, two formulas to join your pieces of text! Which one do you like better?