7 Ways to Combine First and Last Names in Microsoft Excel

By Bhaskar Sharma Updated August 29, 2024 Reviewed & Updated by Afam Onyimadu

Key Takeaways

Flash Fill in Excel efficiently merges names, especially with large files.
Advanced users can perform a lot of name merging using VBA Scripts.
CONCAT and CONCATENATE are two merging functions you may use interchangeably for most cases.

Method 1: Using Flash Fill

Flash Fill is an intelligent tool in Excel that eliminates the need for complex formulas and manual data manipulation. It is the easiest solution if you want to merge many names.

Step 1: Launch your document.

Step 2: Manually input the value for the first one or two roles, combining the first and last names for two roles.

Step 3: Go to the next cell and start typing the next person’s full name. Excel will show you suggestions based on your previous output. Hit Enter to accept the suggestions.

Step 4: If you can’t see suggestions, go to the Data tab and click Flash Fill.

Tip: Press Ctrl + E on your keyboard to trigger the shortcut. Also, you may follow special steps if you notice the slow Excel response time.

Method 2: Using the & (Ampersand) Symbol

The ampersand symbol (&) acts as a catalyst, facilitating the amalgamation of disparate data. You can place it between the cells containing the first and last names, and Excel will seamlessly merge the content into a single cell. Adhere to the steps below to use this method:

Step 1: Launch your desired Excel document.

Step 2: Click into the first cell where you want the merge to occur. Type the command below and hit Enter. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.

=A2&" "&B2

Step 3: Now, to use the same formula for all cells, click on the bottom right of the cell and drag the small square down to copy it into all the remaining cells.

Method 3: Via the CONCAT Function

CONCAT essentially means combining or joining things together. The CONCAT function in Excel allows you to merge different text strings into one effortlessly. Here’s how to use it:

Step 1: Launch the Excel file with the names that need merging.

Step 2: Verify that the first names are in one column and the last names are in another. Double-click the cell where you want the combined full name to appear.

Step 3: Type the command below and hit Enter. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.

=CONCAT(A2," ",B2)

Step 4: To apply the formula to other cells, use the AutoFill handle. Hover over the bottom right of the cell with the formula until you see a small square (the fill handle). Then, drag it down or across to copy the formula to adjacent cells.

Also Read: How to compare columns in Excel for matches

Method 4: Using the CONCATENATE Function

This works very similarly to the CONCAT function. However, it may allow you to join more than two strings.

Step 1: Launch the Excel file with the names that need merging.

Step 2: Verify that the first names are in one column and the last names are in another. Double-click the cell where you want the combined full name to appear.

Step 3: Type the command below and hit Enter. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.

=CONCATENATE(A2," ",B2)

Method 5: Using the TEXTJOIN Function

The TEXTJOIN function in Excel can combine text from multiple ranges or individual text strings based on a specified delimiter. Here’s how to use it:

Step 1: Open the spreadsheet to merge the first and last name columns by double-clicking on it.

Step 2: Head to the cell where you want the combined names to start and input the formula below. We used A2 and B2 because the first name is in cell A2, and the last name is in cell B2.

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

Step 3: Drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to the entire column. This will combine all first and last names.

Drag the fill handle down to apply the formula

Note: TRUE indicates that the function should ignore empty cells.

Method 6: Via Power Query

Power Query is your trusty sidekick that helps clean, shape, and merge your data effortlessly. Adhere to the steps below to merge first and last name columns in Excel using Power Query.

Step 1: Launch the Excel file with the names for merging.

Step 2: Select the range of cells containing the first and last names. Go to the Data tab and click From Table/Range to open the Power Query Editor. Select OK when prompted.

Step 3: Choose the first and last names columns in the Power Query Editor. Click on the Add Column tab > select Merge Columns.

Step 4: Choose a delimiter (e.g., a space) for combining names. Rename the column and click OK.

Step 5: Close the window by pressing the X icon. When prompted, select Keep.

Your data with merged first and last names is now ready in Excel and will be shown in a new column.

Method 7: Using a VBA Script

A custom script offers a powerful and flexible way to combine first and last names in Excel, especially for experienced users comfortable with VBA. This method is especially useful for large datasets or complex formatting requirements, allowing for precise customization in how the names are merged.

Step 1: Open the Excel workbook where you want to combine first and last names.

Step 2: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 3: In the VBA editor, right-click any item in the Project Explorer window on the left. Choose Insert > select Module.

Step 4: Copy and paste the code below and close the window to save it.

Sub CombineNames()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Change "Sheet1" to your actual sheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last row with data in column A (assuming first names are in column A)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row with data
    For i = 2 To lastRow ' Assuming row 1 is headers
        ' Combine first name (column A) and last name (column B), and place the result in column C
        ws.Cells(i, "C").Value = ws.Cells(i, "A").Value & " " & ws.Cells(i, "B").Value
    Next i
End Sub

Note: Adjust the sheet name and column references (Sheet1, columns A, B, and C) to match your actual data layout in Excel. Also, ensure that your data starts from row 2, assuming row 1 contains headers.