How to Combine First and Last Names in Excel?

In today’s digital age, Microsoft Excel remains an indispensable tool for organizing and managing large datasets.

One common challenge many users face is the task of merging separate columns of data. For example, you may have the first name in one column and the last name in another column, and you want to combine the first and the last name. 

There are many easy ways to do this in Excel.

This article delves into five user-friendly techniques to seamlessly combine first and last names in Excel, ensuring your data remains organized and accessible.

Method 1: Using the CONCATENATE (or CONCAT) Function to Combine Names

Let’s see the steps to use the CONCATENATE function.

Let’s take the below dataset where I have the first name in column A and the last name in column B, and I want to combine and get the full names in column C 

Using the CONCATENATE function, you can easily combine the values of both columns. The CONCAT function combines the content from numerous ranges and/or strings. 

To use the CONCATENATE function, you need to type the formula below in cell C2:

=CONCATENATE(A2," ",B2)

The above formula uses A2 and B2 as cell reference that contains the first and last name and gives us the full name. Since I wanted a space character between the names, I added a space character as the second argument.

Step 3: To get the results for the entire list, select the cell that has the formula result, and drag the plus option (called fill handle) till the cell in the column. The result will be something as follows:

CONCAT is the New CONCATENATE

From Excel 2016 onwards, Excel has also introduced a CONCAT function, which works just like the CONCATENATE function. As of writing this article, both functions are available in Excel, but in the future, the CONCATENATE function will be archived.

If you’re using Excel 2016 or higher versions, you can use the below formula as well.

=CONCAT(A2," ",B2)

Method 2: Using TEXTJOIN Function to Combine Names

TEXTJOIN is a common function found in Excel for Microsoft 365 and Excel 2019, helping you to join two text values.

Let’s learn how to use TEXTJOIN, especially without a delimiter, which can be helpful in joining two text values, like first name and last name. 

Let’s take the below dataset where I have the first name in column A and the last name in column B, and I want to combine and get the full names in column C 

Step 1: Using the TEXTJOIN function is straightforward, and here is the syntax: 

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

Step 2: As a result, you will see the First Name in A2, i.e., Matthew, being combined with the Last Name in B2, i.e., Stark, with a space character in between. The result will be Matthew Stark in Column C, Combined Name. 

However, to learn about the TEXTJOIN function, you need to understand its arguments. 

• The first argument is the character or characters you want to use to separate the text. In this example, it’s a space between the first and last name. 
• The second is a True or False statement, where True means that if any of the cells are blank, they will be ignored when the function combines the text.
• The range of cells is in the next argument you want to combine. This tells the function which cells to look at when combining the text.

Step 3: To get the function on all cells, hold on to the small plus sign at the end of the cell selected and drag till the end of the last entry. You will have the following results. 

Since this function is available in Excel 2019 and Excel for Microsoft 365, you won’t be able to use it if you’re using older versions. Also, if you share your workbook (in which you have used the TEXTJOIN function), with someone who is using an older version of Excel that does not have this formula, they will see an error.

TEXTJOIN is a much more powerful and easier-to-use function than the CONCAT function. So if you have access to it, I recommend you use it.

Here are some advantages of using TEXTJOIN over CONCAT or CONCATENATE:

• Instead of manually entering every cell reference, you can select a range in TEXTJOIN. For example, if you want to combine cells A1 to A5, you can use A1:A5 in TEXTJOIN, but you will have to use A1, A2, A3, A4, A5 in CONCAT function
• If there are blank cells in the range, TEXTJOIN function can ignore them (you can specify that in the second argument)

Also read: How to Remove Dashes in Excel?

Method 3: Using the Ampersand (&) Symbol

Excel provides a shorthand method for concatenation using the ampersand symbol. To achieve this, follow these steps:

Let’s take the below dataset where I have the first name in column A and the last name in column B, and I want to combine and get the full names in column C 

Step 1: Select an empty cell for the combined names.

Step 2: Enter the formula =A2&” “&B2 (assuming the first name is in cell A2 and the last name is in cell B2). This formula combines the first name, a space (” “), and the last name using the ampersand symbol.

Step 3: Press Enter or Return to apply the formula, and the combined name will appear in the destination cell.

Step 4: Now select the first combined name cell and drag the cursor to the last name cell.

Dragging the cursor to the last cell will display the combined names.

Method 4: Using Flash Fill to Combine Names

Introduced in Excel 2013, Flash Fill recognizes patterns in your data and then automatically completes the remaining data for you.

Flash-fill is probably the fastest and requires no formula, making it almost magical. 

Here is the trick to doing it. 

Let’s take the below dataset where I have the first name in column A and the last name in column B, and I want to combine and get the full names in column C 

Step 1: To get the desired results, you need to start typing the result in the first entry manually. Following the same, you need to type the second entry. The Flash Fill will understand the pattern of the entry on doing so. 

Step 2: In the next entry, you can see that the suggestion is automatically available, as given in the picture below (the light grey text). Hit enter to get the results, and you have the data set combined in column C. 

Alternatively, you can enter the first result in cell C2 manually, then select cell C4 and use the keyboard shortcut Control + E (hold the control key and then press the E key)

While Flash Fill is fast and easy way to do this, here are some limitations you need to know about:

• Pattern recognition may not always work: Flash Fill works based on patterns, meaning the tool might not work as expected if the data does not follow a recognizable pattern.
• Limited to Adjacent Columns: Flash Fill generally works with adjacent columns. This makes it less flexible if the data you want to work with is spread across non-adjacent columns.
• Not Dynamic: Unlike formulas, Flash Fill does not dynamically update the data. If the source data changes, you’ll have to re-run Flash Fill to update the output.
• Text-Based: It is mainly geared towards text manipulation and is not designed to perform complex calculations.
• Requires Human Verification: Since Flash Fill is pattern-based and not rule-based, there’s always the risk of it not catching exceptions, which means that you still have to verify the output to ensure accuracy.

Method 5: Using VBA (Visual Basic for Applications)

This method offers more flexibility and can handle more complex scenarios. You don’t need to know programming. Simply copy the code and follow the steps as mentioned. You will have the final output as combined first and last names in Excel. 

Here are the steps to follow:

Step 1: Look for the ‘Developer’ tab in the ribbon and select ‘Visual Basic’ OR 

Press ALT + F11 to open the VBA editor in Excel

Step 2:  Click on ‘Insert’ and select ‘Module’ from the drop-down

Step 3: A new dialog box will open, as shown in the screenshot

Step 4: Copy and paste the following VBA code into the module window:

Sub CombineNames()

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long

    ' Define which worksheet you're working on; "Sheet1" is the default 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 and combine names
    For i = 1 To LastRow
        ' Assuming first names are in column A, last names in column B
        ' and combined names will be in column C
        ws.Cells(i, 3).Value = ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value
    Next i

    ' Inform the user
    MsgBox "Names combined successfully!", vbInformation, "Done"

End Sub

Step 5: Click on the ‘Run’ icon or press F5

Step 6: A pop-up will appear once the VBA is run. The names are merged together in the final column

Step 7:  Close the VBA editor.

While VBA is the most cumbersome method of these all it could be suited in your situation if you want to do this quite often. In that case, you can create this VBA code which can then be reused again and again in the same workbook with a single click

Note: If you want to reuse the VBA code you will have to save your workbook as a macro-enabled file (with .xlsm extension)

In this article, I have covered five techniques you can use to combine the first name and the last name in Excel. The easiest way would be to use Flash Fill. You can also use the TEXTJOIN or CONCAT formula to quickly get the results.

Other articles you may also like:

• How to Remove Dollar Sign in Excel Sheet