Categories
Excel

How to Merge First and Last Names in Excel (3 Ways)

There are many reasons why you might need to combine first and last names in Excel.

Whether you’re working with a data list filled full of contact info or you’re creating a sign in sheet for Microsoft Excel training, the need to merge first and last names is just something that seems to come up from time to time.

If it sounds like I’m speaking from personal experience… I am!

In this article, we’ll look at 3 ways to combine first and last names in Microsoft Excel.

1) Combine First and Last Names with CONCAT or the CONCATENATE Function

CONCAT is a text function used to join two or more strings together. CONCAT is short for concatenate… which means “to join” or link things together.

Please note: CONCAT has replaced the CONCATENATE function. While you may still use the CONCATENATE function, it’s included in Microsoft Excel for backward compatibility and support for the function could be discontinued at some point in the future. I would recommend using the CONCAT function instead.

In the spreadsheet below, I entered the following function in cell C2:

=CONCAT(B2, ” “, A2)

Essentially, the CONCAT function joins the first and last names together, separated by a space.

CONCAT function example
The CONCAT function

If you want to display “last name, first name” in column C instead, type the CONCAT function with a comma enclosed in quotes like this:

=CONCAT(A2, “, “, B2)

2) Use the Ampersand to Combine First and Last Names

Using an ampersand is another way join first and last names – and you don’t need to type the CONCAT function… who needs all those brackets and commas anyway!!

In the spreadsheet below, I entered the following formula in cell C2:

=B2 & ” ” & A2

This formula combines the first name from column B with a space and the last name from column A.

Using ampersands to join text
Joining text with ampersands

3) Use Flash Fill to Combine First and Last Name in Excel

Flash Fill is my favorite to way to combine first and last names in Excel. If you haven’t seen Flash Fill in action, I think you’re going to love it… it almost works like magic!

If you have a list of first and last names in Excel, you click a cell in an adjacent column and type the full name in the list exactly as you would like it to appear then press Enter.

When you start typing the next name, Excel automatically enters the remaining names for you, using the first entry as an example.

In the screenshot below, I typed Abigail Aalderink in cell C2 then pressed Enter. In the next row, as soon as I typed S, Flash Fill automatically generated the remaining entries. To accept Flash Fill’s list, I pressed Enter again.

Flash Fill
Use Flash Fill to combine names

What’s Next?

I hope you enjoyed reading about various ways to merge first and last names in an Excel list. If you want to continue learning about functions, you might want to check out this article on how to replace spaces with an underscore in Excel.

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.