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.

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.

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).

Did you learn how to merge first and last name in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.