Excel Formulas: Using CONCATENATE to Combine Names

Lesson 18: Using CONCATENATE to Combine Names

/en/excelformulas/using-the-if-function/content/

illustration of character

"Hey, what's up? So, I know we were supposed to grab some Thai food tonight, but I'm stuck at work.

My boss wants me to copy a list of names from a spreadsheet to a text document. But when I copy and paste, the formatting ends up all weird. I think it'd work better if the first and last names were in the same column, but it'll take forever to type all that."

Our spreadsheet

Once you've downloaded our spreadsheet, open the file in Excel or another spreadsheet application. It looks like we have a list of contact information. Each person has his or her own row, and there are columns for each person's first name, last name, and other contact information.

screenshot of excel 2013

How can we solve this problem quickly?

We want the information from the Last Name and First Name column to appear together in the same cell, but it would take a long time to type everything by hand. Rather than combining this data manually, we can use the CONCATENATE function to do it automatically.

How it works

The word concatenate is just another way of saying "to combine" or "to join together". The CONCATENATE function allows you to combine text from different cells into one cell. In our example, we can use it to combine the text in column A and column B to create a combined name in a new column.

Before we start writing the function, we'll need to insert a new column in our spreadsheet for this data. In our example, we'll insert it to the right of column B.

screenshot of excel 2013

Writing the function

We're ready to enter our function into cell C2. As usual, we'll start with the equals sign (=) followed by the function name and an open parenthesis:

=CONCATENATE(

In the current version of Excel, you can use the new CONCAT function instead of CONCATENATE. The two functions work the same way.

Now we're ready to enter our arguments. The arguments tell the CONCATENATE function what cells to combine. In our example, we want to combine the text in cells A2 and B2, so we'll make each of those an argument:

=CONCATENATE(B2, A2)

OK, let's run this function!

screenshot of excel 2013

You may have noticed that the first and last names don't have a space in between them. That's because CONCATENATE will combine exactly what you tell it to combine, and nothing more. If you want punctuation, spaces, or any other details to appear in the cell, you’ll need to tell CONCATENATE to include it.

To add a space, we can simply add another argument: " " (two double quotes around a space). Make sure the three arguments are separated by commas:

=CONCATENATE(B2," ",A2)

screenshot of excel 2013

Perfect! Now we can use the fill handle to copy the formula to the remaining cells in this column.

screenshot of excel 2013

That's it! We're ready to hand this back to our friend:

illustration of character

"Hey, this is sweet! That was so easy, and now I can copy and paste these names as a single column.

Thanks for helping me figure this out so fast! Your Pad Thai is on me tonight!"

Bonus Section

/en/excelformulas/buying-a-printer-cost-comparison/content/