How to combine First and Last Names in Microsoft Excel

Are you looking for easy ways to combine First and Last Names in Microsoft Excel? When dealing with large quantities of contacts as in the case of email lists, first name and last name are often presented in different columns. One way to reduce or condense your columns is to merge these into one column labeled as a “Full Name” field which will make your work much easier.

To solve this problem, there are several techniques in the Microsoft Excel which a user can apply without any hassles. This guide features seven techniques on how to join first and last names in Microsoft Excel in Windows while avoiding inconveniences.

How to combine First and Last Names in Microsoft Excel

Method 1: Using the & (Ampersand) Symbol

The & symbol comes in handy when joining two or more text values together in excel without necessarily having to use many functions.

1. Right-click on the cell that will contain the merged name.

2. Enter this below formula-

=A2 & " " & B2

A2 takes first name and B2 takes the last name while “ ” inserts a space in between.

3. Press Enter and then select the cell below and drag the formula to find the value of others.

Method 2: Using the CONCATENATE Function

Although CONCATENATE has been replaced by CONCAT in the latest versions of Excel it is still very popular in earlier versions.

1. Right click on the cell where you want to type the combined name.

2. Enter the formula-

=CONCATENATE(A2, " ", B2)

3. Press Enter and drag down to apply to other rows and change them at the same time.

Method 3: Via the CONCAT Function

CONCAT is the latest function, which is better than CONCATENATE when used in joining more values.

1. Choose the cell for the combined name option.

2. Enter the formula-

=CONCAT(A2, B2)

3. Press Enter and then the formula will be copied to other cells of the chosen column.

Method 4: Using the TEXTJOIN Function

TEXTJOIN is superior to CONCAT in the way that when using multiple columns or when you want to skip blanks, then TEXTJOIN is handy. It lets you join two or more text values with a specific character like space or comma.

1. Click the cell containing the full name.

2. Enter the formula-

=TEXTJOIN(" ", TRUE, A2, B2)

The first argument is the delimiter (here, a space) and the TRUE argument means that Embrace will not attempt to read the values from empty cells.

3. Press enter and choose the cell, then use copy paste to spread it downwards.

Method 5: Using Flash Fill

Flash Fill is one of the great, automatic utility tools that analyze patterns and enter the rest of the blanks on their own.

1. We enter the first managed name in a new column by typing the name right (for example, “Kapil Arya”).

2. Go down to the next cell in the row, type the names you want to combine and click the “Flash Fill” option. Excel will auto fill in the rest.

3. Press enter to confirm the suggestions provided and complete the rest of all the cells.

Method 6: Via Power Query

Power Query is one of Excel features that facilitate data manipulation to a next level as compared to the traditional tools. It is very useful in the processes where much repetitive work is to be done or the bulk volume of data sensitive to manipulate.

1. Choose the data that you wish to use with the other data set, then select Data > From Table/Range.

2. In Power query, choose both the first and last name columns.

3. Right click and select merge columns.

4. In the dialog box, select the options, often typically a space or a point and click OK.

5. By clicking Close & Load button, the changes to the data will reflect in excel.

Method 7: Using a VBA Script

If you often have to merge first and last name, then it is possible to create VBA script for it.

1. In order to start editing use Alt + F11 to get into the VBA editor. Locate Insert on your menu bar then click on Module.

2. Paste the following code and press F5 to run this script.

Sub CombineNames()
    Dim LastRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To LastRow
        ws.Cells(i, 3).Value = ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value
    Next i
End Sub

This code supposes that first names lie in the column A and last names lie in the column B, and in the column C they are united.

3. Now, hit Alt+F8 and Run the Macro.

Though, if you experience any error while performing any of these methods on how to combine First and Last Names in Microsoft Excel, leave a comment below so we can help you out.

That’s it!