How to Combine First and Last Name in Excel

Excel has many useful functions and tools that enable us to perform text manipulation. That’s why, when you have first and last names in separate columns in a spreadsheet, it’s extremely easy to combine them into a full name, whether you want it separated with a space or a comma.

In this tutorial, we will learn how to combine first and last names in Excel using the CONCATENATE function, the ampersand (&) operator, and Flash Fill.

Using the CONCATENATE Function to Combine First and Last Name

The CONCATENATE function enables us to combine multiple cells or values. We can use this function to combine first and last names and separate them with a space or a comma. Let’s learn how…

Write the following formula in a new cell to combine first and last names with a space:

=CONCATENATE(A2, " ", B2)

The first cell name (A2) refers to the first name, and then we concatenate the space character (“ “), and the last name (B2).

Use CONCATENATE to combine with space

You can drag the small green rectangle in the bottom right of the cell with the formula, and drag it downward to apply the concatenate operation to other cells.

Drag downward to apply concatenate to other cells

If you want to combine the names with a comma with the format “LastName, FirstName”, use the following formula:

=CONCATENATE(B2, ", ", A2)
Use CONCATENATE to combine with comma

The CONCATENATE function can combine multiple text values or cells. Therefore, it can also be used to combine a first name, middle name, and last name:

=CONCATENATE(A2, " ", B2, " ", C2)
Combine middle name with CONCATENATE

So, the trick is: to write all the cell values and separators inside the CONCATENATE formula and separate them with commas. When you learn this simple formula, you can combine anything!

Using the Ampersand (&) Operator Function to Combine First and Last Name

Text combination can also be achieved with the ampersand (&) operator. Write the following formula to combine first and last names with a space:

=A2&" "&B2
Use ampersand to combine with space

If you want to combine the names with a comma, type the following formula in a new cell:

=B2&", "&A2
Use ampersand to combine with comma

The ampersand operator (&) works as follows: you need to start the formula with the equal (=) sign, and then write the cell names and separators, and type ampersand (&) between each of them. This is another elegant way of combining first and last names.

Using Flash Fill to Combine First and Last Name

Without using operators and formulas, it is perfectly possible to combine first and last names: with the help of Flash Fill. Flash Fill is a tool that learns and applies patterns. Follow the steps below to combine using Flash Fill.

Step 1: Next to the first and last names, write the full name in a new cell.

Write full name in a new cell

Step 2: Go to the cell below by pressing Enter, or clicking on the cell.

Go to the cell below

Step 3: In the Home tab, click on the Fill icon in the Excel ribbon, and then click on Flash Fill.

Click on Flash Fill

Flash Fill will automatically learn the pattern, and combine the first and last name for all cells.

Flash Fill combines all names

You can apply the same steps if there are middle names in your data. As long as you type the full name exactly as in the original cells that contain the first, middle, and last names, the combination pattern will be learned and applied by Flash Fill.  

In this tutorial, we learned how to combine first and last names in Excel using the CONCATENATE function, the ampersand (&) operator, and Flash Fill. You can choose whichever method suits you best.

You may want to check how to switch first and last names in Excel or sort by last name in Excel.

Leave a Comment