How to Merge First and Last Name in Excel with Comma (Step by Step)

When working with spreadsheets that contain names, keeping first and last names in separate columns is often convenient. This is because it makes filtering and querying easier. However, there may be times when you need to merge the first and last names back into one column. Fortunately, you don't need to do this manually. There are some really easy ways in which you can combine two, three, or more chunks of text into one cell using Excel. In this tutorial, we will look at two such methods.

Formulas to Merge First and Last Name

There are two formulas in Excel that let you merge first and last names into one cell. Let's look at them one by one.

1. The ampersand (&) Method

The ampersand (&) is more of an operator than a formula. It is mainly used to join several text strings into one. Here;s how you can use it to merge your first and last names.

Let us assume you have the following set of first and last names:

Below are the steps to merge the first and the last name using ampersand:

1.Click on the first cell of the column where you want the combined names to appear (C2).

2.Type equal sign (=).

3.Select the cell containing the first name (A2) followed by an ampersand (&)

4.Select the cell containing the last name (B2)

5..Press the Return Key.

6.You will notice that the first name and last name are combined together alright, but without any space in between. To change that, you can add space within the formula. Add & after the cell reference for the first name. So for our example, your formula now is =A2& &B2..

7.Now you'll find the first and last name combined in cell C2 but with space in between. Perfect! It';s now time to copy this formula to the rest of the cells in the column. Simply double click the fill handle (located at the bottom right of cell C2).

That's all, all your cells in column C3 contain full names in each row.

2. The CONCATENATE () FUNCTION For Merging First and Last Name in Excel

1. The CONCATENATE () function provides the same functionality as the ampersand (&) operator.

2. The only difference between the two is the way they are used.

3. Let’s use the same dataset as above and apply the CONCATENATE function to it:

4. Click on the first cell of the column where you want the combined names to appear (C2).

5. Type equal sign (=).

6. Enter the function CONCATENATE, followed by an opening bracket.

7. Select the cell containing the first name (A2) followed by a comma(,)

8. Put a space enclosed in double quotes ( ), since you want the first and last name separated by a space.

9. Select the cell containing the last name (B2). In our example, your formula should now be: =CONCATENATE (A2, ,B2).

10. Press the Return Key.

11. Now you'll find the first and last name combined in cell C2 with space in between. Copy this formula to the rest of the cells in the column by double-clicking the fill handle (located at the bottom right of cell C2).

3. Combine Last Name and First Name With Comma

Do you wish to combine the last and first names with a comma? Well, here’s how you can do this. Follow the steps mentioned below.

Step 1: Many people want to combine names in Last Name, First Name format. Move on to step 2 now.

Step 2: The first formula is =last_name_cell&” , “&first_name_cell. These formula will look like =B2&” , “&A2.

This formula is basically the same one mentioned above, but with names in reverse order and separated by a comma and space.

Step 3: The other formula is =CONCATENATE(last_name_cell, “, “,first name). This formula will be shown as =CONCATENATE(B2, “, “,A2). This is also the same formula mentioned above but with names mentioned reverse with a comma and space in between.

Step 4: The screenshot below shows cell C2 with the formula of =B2&” , “&A2.

Step 5: The cell D2 in the screenshoot below contains the formula as =CONCATENATE(B2, “, “,A2).

Step 6: You just need to follow the same procedure mentioned above with the same formulas but names in reverse and a little bit of tweak for the comma. However, whichever formula you follow, the results will be the same.

Step 7: After adding the formula, you can press the return key (Enter) to apply. You’ll see that the names are combined in the Last Name, First Name format with a comma and space between them.

Step 8: To apply this formula on every cell of the column, you need to copy the formula and double-click the fill handle, which is located at the bottom right of cell C2.

4. Join First, Middle, and Last Name in One Cell

Joining a person's first, middle, and last names in Excel can be very tiring. But we are here to help. Follow the below-given steps to join first, middle, and last names in one cell.

Step 1: The formula to combine three different names listed in 3 separate columns is the same as before but a few more changes this time.

Step 2: Assuming that the first name is in A2, the middle Nae is in B2, and the last name is in C2, you can use the two formulas mentioned below.

Step 3: The formulas to combine the first, middle, and last name in one cell are =first_name_cell&” “&middle_name_cell&” “&last_name_cell

The other formula is CONCATENATE(first_name_cell,” “,middle_name_cell,” “,last_name_cell)

Step 4: You can use the above-mentioned formulas and see the first, middle, and last names combined in one cell. However, often times there is no middle name. In that case, you can handle each case individually and then combine two formulas into one with the help of an IF statement:

=IF(B2=“”, A2&” “&C2, A2&” “&B2” “&C2)

Step 5: With the help of the IF statement, you can prevent the appearance of extra spaces between words in rows where a middle name is missing.

Step 6: You can simply follow the steps above to combine the first, middle, and last names in one cell. To apply this to all cells I’m column, you need to copy the formula and double-click the fill handle.

5. Merge First and Last Name using Flash Fill

You can merge first and last names without a formula using Flash Fill. To merge the first and last name in Google Sheets using the flash fill method, follow the instructions below clearly.

Step 1: Select the place (cell) where you want the results, i.e., combined names.

Step 2: After clicking on the first cell, fill the cell with the first and last name. The first one needs to be done manually.

Step 3: You will see the Data tab in the tabs menu. Under the Data tab, click the Flash Fill button (in the Data Tools group). Alternatively, you can just press CTRL+E on your keyboard (Command+E in case you are a Mac user)

Step 4: It’s done! The Flash Fill tool will merge your first and last name without a formula in seconds, saving you energy and time.

FAQs

How to merge first and last name in Excel automatically?

You can merge first and last names in Excel automatically or without a formula using the Flash Fill tool.

How do I put a space between first and last names in Excel?

You can put a space between the first name and last name in Excel by adding the space within the formula by adding &” “ after the cell reference for the first name, and your formula should look like =A2&” “&B2.

How to combine first and last name in sheets?

To combine first and last names in sheets (Google Sheets), you can follow the same procedure steps used for Excel, I.e., =A2&” “&B2.

Summary

Millions of people use Excel; however, very few people know about these methods to combine first and last name, last and first name, and first, middle, and last name in Excel. These methods are essential because they save you time and energy to spend on other things. Moreover, they make your work easier.

WPS Office offers a single platform for four different platforms to increase your productivity and creativity further. You can use PDF, PPT, Excel, and Word in a single application. WPS Office is an office suite used by millions of people. The application aims to increase the productivity and creativity of people with its user-friendly and easy-to-use interface. Don’t wait download yours now!