How to Combine First and Last Name in Excel (4 Easy Ways)

Excel is an amazing tool to store and analyze data. And many of the times, you will have to deal with text data types such as names, regions, departments, or product names.

In such cases, it’s good to know how to manipulate text data and get the desired result.

One of the most common tasks most Excel users have to do is work with a dataset of names. Often you’ll find that the first name and the last name are in separate columns, and you may have a need to combine these first and last names and get these as a combined name in a cell.

In this Excel tutorial, I’ll show you multiple different ways to combine the first and the last name in Excel.

You can easily do that using simple formulas (such as Concatenate or TextJoin) and features such as Flash Fill and Power Query

4 Ways to Combine First and Last Name in Excel

Suppose you have a dataset as shown below and you want to combine the first name in column A and the Last Name in column B.

Let’s have a look at some of the ways to do this.

Using CONCATENATE Function (or Ampersand)

Combining different text strings from different cells is quite easy in Excel. There is an in-built Concatenate formula in Excel that’s made for this purpose only.

Below is the formula that will combine the first and the last name (separated by a space character):

=CONCAT(A2," ",B2)

CONCAT is a new function that was introduced in Excel 2016 and is made to replace the CONCATENATE function. But as of now, both the functions continue to be available and you can either function.

Below is the CONCATENATE formula if you wish to use that:

=CONCATENATE(A2," ",B2)

The above formula simply takes the first and the last name and combines it. And since I want these to be separated by a space character, I have used ” ” (space in double-quotes) as the second argument.

You can also use the ampersand operator to do the concatenation.

Assuming you have the same dataset, you can use the below formula to combine the first and the last name:

=A2&" "&B2

The ampersand operator combines the text before and after it. In the above example, I have combined three parts – first name, a space character, and last name.

Now that you understand the concept, you can combine the names in different formats if you want. For example, you may want to have the last name and then the first name, or a comma instead of the space between the names.

In case you only want the combined name and want to get rid of the first and the last name, you should first convert the formula values to static values. Once done, you can then remove/delete the first and the last name.

Using the TEXTJOIN function

TEXTJOIN is a function that’s available in Excel 2019 and Office 365.

In case you have access to this function, it’s best to use it for combining cells and columns (as it’s a lot better than the above CONCATENATE and ampersand methods).

Suppose you have the dataset as shown below and you want to combine the first and the last name.

Below is the formula to do this:

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

The above TEXTJOIN function takes three arguments:

1. The delimiter, which is a space character in double-quotes in this example (since we want the first and the last name to be separated by a space character)
2. A Boolean value where TRUE means that in case there are any blank cells, the formula will ignore it
3. The range that has the cells that you want to combine

It’s faster than the regular concatenate formula and is also easier to create and manage. So if you have access to the TEXTJOIN function in Excel, it’s better to use it over any other formula.

Using Flash Fill

Flash Fill is a smart functionality that tries to understand the pattern and give you the result.

Let me explain how it works.

Suppose you have a dataset as shown below and you want to combine the first and the last name.

Below are the steps you can use to do this using Flash Fill

1. In cell C2, enter the result you want. In our example, it would be ‘Bobby Baker’
2. In cell C3, start typing the expected result. You will notice that Excel shows you the expected result in all the cells (in the light gray text). This is Flash Fill in action

3. Hit the Enter key

The above steps would instantly fill all the cells with the combined name.

In some cases, it’s possible that you won’t see flash fill while you are typing in the second cell.

Don’t worry, it happens sometimes.

In such a scenario, below other steps you can use to make Flash Fill work:

1. In cell C2, enter the result you want. In our example, it would be ‘Bobby Baker’
2. Select select C3
3. Click the Home tab

4. In the Editing group, click on the Fill icon
5. Click on Flash Fill

The above steps would instantly pick the pattern from the cell above and fill the entire column with the combined name.

In case Flash fill isn’t able to pick up the right pattern and gives incorrect result, fill two cells manually and then do the above steps.

You can also use the keyboard shortcut Control + E to fill using Flash fill.

Combining the first and the last name (or even the first, middle, and last name) is a simple operation that Flash Fill can easily handle.

Keep in mind that Flash Fill is not perfect. It works by identifying patterns and using the same pattern to fill all the cells in the column. While it’s most likely to work as expected, it’s a good idea to double-check the result of Flash Fill.

Using Power Query

Power Query is an amazing tool that used to extract and transform data.

You can also use this to quickly merge columns and combine the first and the last name.

For Power Query to work, your data needs to be in an Excel Table (or at least a Named Range).

For the purpose of this tutorial, I will convert the data set that has the 1st and the last name into an Excel Table.

Suppose you have a data set as shown below and you want to merge the two columns to get the full name.

Below are the steps to convert the data into an Excel Table:

1. Select any cell in the dataset
2. Click on the Insert tab

3. Click the Table icon

4. In the Create Table dialog box, make sure the range is correct

5. Click on Ok

The above steps would convert the data range into an Excel Table.

Now let’s see how to combine the first and last name using Power Query:

1. Select any cell in the table
2. Click the Data tab

3. In the Get & Transform Data group, click on the ‘From Sheet’ option. This will open the Power Query Editor

4. In the Power Query Editor, make sure the right table is selected in the left pane. If you just have one table, you will only see one option in the left pane
5. Select the columns that you need to merge (hold the Control key and then click on the column header to select the column)

6. Right-click and then click on the ‘Merge Columns’ option

7. In the Merge Columns dialog box that opens, select Space as the delimiter (from the drop-down)

8. Enter a name for the new merged column. I will go with ‘Full Name’

9. Click OK. This would remove the two columns that we already have and replace them with the new column that has the full name.

10. Click the File tab and then click on Close and Load.

The above steps would insert a new worksheet in the workbook with a table that has one column that has the full name.

In case you want the original columns as well as a new column that contains the full name, instead of selecting the columns and Merge them, first select the Add Column tab in the Power Query Editor, then select the columns, and then click on Merge Columns. This will add a new column that will have the full name.

Compared to the formula methods and Flash Fill, Power Query is definitely a bit longer.

But the benefit of using this method is that in case your original data changes, you can quickly refresh the query and your resulting data would automatically update.

Also, Power Query is widely used to combine different tables and data from multiple worksheets and workbooks. So, in case you have the name data you want to combine, it’s one single step in your already existing power query workflow.

In this tutorial, I have covered how to combine the first name and the last name in Excel. But in case you have the first, middle, and last name, you can use the same methods to do it.

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

• Separate First and Last Name in Excel (Split Names Using Formulas)
• How to Sort by the Last Name in Excel
• How to Extract a Substring in Excel (Using TEXT Formulas)
• Extract Usernames from Email Ids in Excel
• How to Extract the First Word from a Text String in Excel
• Extract Last Name in Excel (5 Easy Ways)
• Separate Text and Numbers in Excel
• How to Switch First and Last Name in Excel with Comma?