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

You can combine a first name and a last name into one cell in Excel with a simple formula, or with features that don’t need a formula at all.

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 the ampersand, CONCAT, or TEXTJOIN) and features such as Flash Fill and Power Query. I’ll also show you how to handle middle names, because a blank one can leave you with an extra space.

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

Combine First and Last Name Using the Ampersand (&) Operator

Let’s start with the simplest formula, which uses the ampersand operator.

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, and get the full name in column C.

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

=A2:A11&" "&B2:B11

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.

You only need to enter this formula in cell C2. It spills down automatically and fills the full name for all the rows, so there’s nothing to copy.

If you are using Excel 2019 or earlier, enter the below formula in cell C2 and then copy it down the range:

=A2&" "&B2

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.

Below is the formula that does both of these, and I’ve entered it in cell D2:

=B2:B11&", "&A2:A11

If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down the range:

=B2&", "&A2

If your names are already in one cell and you want to flip them, that’s a different job, and I’ve covered it in How to Switch First and Last Name in Excel with Comma?

You may also be tempted to select the two cells and use Merge & Center, but that doesn’t combine the names. It keeps only the first name and deletes the last name, so here’s how to merge cells without losing data if you need that.

The full names in this method are formula results, and that’s true for every formula in this article. So if you delete columns A and B, the formulas lose their source and show the #REF! error.

Important: 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.

Merge First and Last Name Using the CONCAT Function

Combining different text strings from different cells is quite easy in Excel, and there are built-in functions that are made for this purpose only.

The CONCAT function is one of these, and it’s a good choice when you prefer a function over the ampersand.

Below is the same list of names, and I want to get the full name in column C.

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

=CONCAT(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 need to enter this formula in cell C2 and then copy it down the column.

CONCAT doesn’t spill like the ampersand formula does. If you give it the full ranges (=CONCAT(A2:A11," ",B2:B11)), it squeezes all the first names and all the last names into one cell.

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 use either function.

Below is the CONCATENATE formula if you wish to use that, for example in Excel 2013 or earlier. Enter it in cell C2 and then copy it down the column:

=CONCATENATE(A2," ",B2)

Microsoft says CONCATENATE may not be available in future versions of Excel, so it’s better to use CONCAT if your version has it.

Using the TEXTJOIN Function

TEXTJOIN is a function that’s available in Excel 2019 and later versions, including Microsoft 365.

In case you have access to this function, it’s best to use it for combining cells and columns when you have more than two of them, because you specify the separator only once and it can skip blank cells for you.

That makes it a lot better than the above CONCAT and ampersand methods in that situation.

To keep things simple, I’ll show you how the formula works on just two columns here, and you’ll see the real benefit in the middle name section below.

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

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

You need to enter this formula in cell C2 and then copy it down the column. TEXTJOIN returns one result per formula, so if you give it the full range A2:B11, you get all the names joined together in one cell.

When you have a lot of columns to combine, it’s faster to set up than a CONCAT or ampersand formula because you select one range instead of listing every cell. It’s also easier to create and manage.

So if you have access to the TEXTJOIN function in Excel and you’re combining more than two cells, it’s better to use it over a plain CONCAT or ampersand formula.

For just a first and last name, the ampersand formula above is the simpler choice. It’s shorter in every Excel version, and in Excel 2021 or later and Microsoft 365, it spills on its own.

Combine First and Last Name Without a 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 in column C.

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 are 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 cell 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. You’ll also find the same Flash Fill option on the Data tab.

In case Flash Fill isn’t able to pick up the right pattern and gives incorrect result, fill two cells manually (C2 and C3), select the next cell (C4), and then click on Flash Fill again.

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.

The results Flash Fill gives you are plain values and not formulas. So if a first name changes later, the full name won’t update, and you’ll need to run Flash Fill again.

Important: 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 is 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 Table/Range’ option (some Excel versions call it ‘From Sheet’). This will open the Power Query Editor

4. The left pane of the Power Query Editor lists your queries, and Excel has just created one from your table (with the same name as the table). Make sure it’s selected. If it’s your only query, 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). Click First Name first, because the order you select the columns in is the order of the names in the result

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

7. In the Merge Columns dialog box that opens, select Space as the separator (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 & Load.

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

Pro Tip: In case you want the original columns as well as a new column that contains the full name, don’t merge them as above. First select the Add Column tab in the Power Query Editor, then select the columns, and 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.

Combine First, Middle, and Last Name

So far, every method has combined two columns. In case you have the first, middle, and last name, you can use the same methods, but a blank middle name needs a little care.

Below is a list of names with the first, middle, and last name in separate columns, where some people don’t have a middle name, and I want to get the full name in column D.

Here is the ampersand formula, extended to three columns:

=A2:A11&" "&B2:B11&" "&C2:C11

If you are using Excel 2019 or earlier, enter the below formula in cell D2 and then copy it down the range:

=A2&" "&B2&" "&C2

Look at cell D2. Bobby has no middle name, so the formula still adds both of its spaces, and you end up with two spaces between Bobby and Baker.

It’s easy to miss on screen, but it means the name won’t match a correctly typed ‘Bobby Baker’ in a lookup. Let’s fix that.

The first fix is to wrap the same formula in the TRIM function, which removes the extra space. I’ve entered it in cell E2:

=TRIM(A2:A11&" "&B2:B11&" "&C2:C11)

Like the ampersand formula, this one spills down on its own, so you only enter it once.

If you are using Excel 2019 or earlier, enter the below formula in cell E2 and then copy it down the range:

=TRIM(A2&" "&B2&" "&C2)

The second fix is TEXTJOIN, and this is where its TRUE argument really helps. I’ve entered it in cell F2 and copied it down the column:

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

TRUE tells the formula to ignore blank cells. So when the middle name is blank, TEXTJOIN leaves it out completely and adds only one space.

Flash Fill can handle this too, but it needs the right example. If you only type Bobby Baker (who has no middle name), Flash Fill leaves out every middle name in the list.

In the example file, column D already has a formula, so clear it first.

In the column right next to the names, fill two cells manually, Bobby Baker in the first and Jesse Noah Turner in the second.

Then select the next cell and run Flash Fill, and it keeps the middle names and still skips the blank ones.

The Power Query steps above don’t skip a blank middle name. If you merge the columns that way, you get two spaces in the result, so one of the formula fixes above is the better choice for a list like this.

In this tutorial, I have covered how to combine the first name and the last name in Excel, along with the first, middle, and last name.

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
• Text.Combine Function in Power Query M
• PROPER Function in Excel
• How to Concatenate a Range in Excel (With or Without a Separator)