How to Merge First and Last Name in Excel?

by

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 combine 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 combine first and last names into one cell. Let’s look at them one by one.

Using 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:

Names Dataset with first and last name

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.

Ampersand to combine first and last name
Also read: How to Generate Random Names in Excel

The CONCATENATE() Function

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

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

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

  1. Click on the first cell of the column where you want the combined names to appear (C2).
  2. Type equal sign (=).
  3. Enter the function CONCATENATE, followed by an opening bracket.
  4. Select the cell containing the first name (A2) followed by a comma(,)
  5. Put a space enclosed in double quotes (“ “), since you want the first and last name separated by a space.
  6. Select the cell containing the last name (B2). In our example, your formula should now be: =CONCATENATE(A2,” “,B2).
  7. Press the Return Key.
  8. 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).
Concatenate formula to combine first and last name

Few things to note about this formula:

  • If you want to put the last name followed by a comma, followed by the first name, then instead of a space, put a comma within quotes. Also, replace the first cell reference with the second cell reference and vice-versa. So for this example, C3 would have the formula: =CONCATENATE(B2,” , “,A2)
  • You can replace the separating character with any character you need.
  • If you have a first name, last name, and a middle name in cells A2, B2, and C2 respectively, your formula in D2 can be =CONCATENATE(A2,” “,B2,” “,C2).
  • If you want to have the first name, followed by the middle name’s initial, followed by the last name, then your formula in D2 can be =CONCATENATE(A2,IF(B2=””,” “,” “&LEFT(B2,1)&”.”),C2). The LEFT() function takes the specified number of characters starting from the left side of the text in a cell. Here, we specified that if cell B2 is not blank (that means if a middle name exists), we want just the first character from the cell B2. We followed this by a dot operator and space. Finally, we concatenated the value in C2 (the last name).
Formula to have first and last name and middle name initial

You’ll notice that since you’re using a formula, the result is dependent on the original cell values of first and last names.

So if you make any changes to the original values, they get reflected in the combined values.

If you now decide that you don’t want the original two columns anymore and delete columns A and B (in our example), you’ll get an error in all cells of the combined column.

Ref Error in case you delete dependent cells

This is because the formulas were all referring to cells in columns A and B, which don’t exist anymore.

To avoid this, the best thing to do is convert the formulas of the combined co to permanent values by copying them and pasting them as values in the same column (Right-click and select Paste Options->Values from the Popup menu).

Paste as Values

Now you can go ahead and delete the original columns if you need to.

Also read: Extract Last Name in Excel

Merge First and Last Name using Flash Fill

This is a really cool technique. This technique takes advantage of Excel’s pattern recognition capabilities.

If you’re using any version of Excel from 2013 onwards, you’ll get Excel’s flash fill functionality.

Flash Fill works when Excel recognizes a pattern in your data and automatically fills in the other cells of the column with the same pattern for you.

Here’s how you can use Flash Fill to combine first and last names:

  1. Click on the first cell of the column where you want the combined names to appear.
  2. Enter the first and last name for the first row manually.
  3. Under the Data tab, click on the Flash Fill button (in the ‘Data Tools’ group). Alternatively, you can just press CTRL+E on your keyboard (Command+E if you’re on a Mac).

This will copy the same pattern to the rest of the cells in the column…in a Flash!

Merge names using Flash Fill

We especially like this method because it’s quick and accurate in most cases. You don’t have to spend time typing formulas

It detects and copies patterns very effectively, including capitalization and punctuation.

For example, if your original first and last names started with small letters, you can capitalize the first letter of the first and last names in the combined cell and when you apply flash fill, it will automatically copy your pattern and capitalize the first letter for all the names.

Combine Names using Flash Fill with capitalized first letter

In this way, you can have names joined exactly the way you like.

Combine Names using Flash Fill with capitalized first letter and comma

In the same way, you can also set the pattern to have the last name followed by a comma, followed by the first name.

Since this method doesn’t involve a formula, it doesn’t depend on the original columns.

So, even if you delete the original two columns containing first and last names respectively, the combined column remains unaffected.

Also read: Remove Middle Name from Full Name in Excel

Conclusion

In this tutorial, we showed you two ways in which you can combine first and last names into a single column in Excel.

It’s important to note though, that these are not the only two ways to do this.

There are plenty of other ways in Excel, like using VB script, or the TEXTJOIN function (available in newer versions and Office 365).

These two were the methods we found to be the simplest and most applicable to all.

I hope you found this Excel tutorial useful!

Other Excel tutorials you may find useful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment