Merge First and Last Names in Excel

Working with a collection of names is one of the most frequent tasks that most Excel users face daily. Frequently, the first and last names are found in different columns. Sometimes, you might need to merge these names to obtain the combined name in a cell.

I will guide you through several methods in this Excel tutorial for combining the first and last names.

You can do this easily with features like Power Query and Flash Fill and basic formulas like Concatenate and TextJoin.

Five Methods in Excel to Merge First and Last Name

Assume you want to merge the First and Last names in columns C, D respectively, of the dataset displayed below.

Let's examine a few methods for achieving this.

1. CONCATENATE Function (Ampersand):

Excel's CONCATENATE function, also known as the ampersand, makes combining text strings from various cells simple. Excel has a built-in Concatenate formula explicitly designed for this use.

The following formula, which separates the first and last names with a space character, will combine them:

The CONCAT function was added to Excel 2016 as a replacement for the CONCATENATE function. However, as of right now, you can use either function, and both are still available.

The CONCATENATE formula is provided below:

All that is needed for the above formula is to combine the first and last names. And for the second argument, I have space (in double quotes) "" because I want these values to be separated with a space character.

Concatenation can also be performed with the ampersand operator:

With the assumption that you are working with an identical dataset, you can merge the first and last names using the formula below:

The text preceding and following the ampersand operator combines it. I have combined the first name, the space character, and the last name in the example above.
You may combine names in various formats now that you know the concept behind them. For example, you might want to put a comma in place of a space between the names, or you might want to put the last name before the first.
You must change the formula data to static values if you only need the combined name and remove the first and last names. After that, you may delete or remove the last and first names.

2. TEXTJOIN function

Office 365 and Excel 2019 both have the TEXTJOIN function available.

If you can access this function, use it instead of the CONCATENATE and ampersand techniques mentioned above for merging cells and columns.

Suppose you need to merge the dataset's first and last names, as displayed below.

Below is the formula you can follow:

To merge first and last names, use the TEXTJOIN formula.

There are three arguments required for the TEXTJOIN function above:

Since we have to separate the first and last names with a space character, the delimiter in this example is the space character enclosed in double quotes.
If the value is Boolean and TRUE, the formula will not consider any blank cells.
The range in which you want to merge the cells

It's simpler to make and maintain and runs faster than the standard concatenate formula. The TEXTJOIN function in Excel is the preferred option over all other formulas if you have access to it.

3. Flash Fill

One innovative function that tries to figure out the pattern and provide you with the outcome is called Flash Fill.

Let's see how it works.

Suppose you must merge the first and last names in a dataset like the one below.

The steps for doing this with Flash Fill are listed below.

Enter the desired outcome in cell C2. It would be 'Gobindh Ram' in our case.
Begin entering the expected result in cell C3. You'll notice that Excel displays the predicted outcome in each cell (in light grey text). This is the case. The action of Flash Fill
The combined names are displayed in flash fill.
Press the Enter key.

The preceding steps would quickly fill all cells with their combined name.

It is possible that you won't notice a flash fill while writing in the second cell in some cases. It happens occasionally, so don't worry.

Here are some additional methods to get Flash Fill to function in such a situation:

Enter your desired outcome in cell C2. 'Gobindh Ram' would be the example in our case.

Choose option C3.
Go to the Home tab.
Select the Fill symbol located in the Editing group.
Select the Flash Fill menu item in the ribbon.

Following the above steps, the combined name would fill the whole column, and a pattern within the above cell would be automatically selected.
Fill two cells by hand and follow the above instructions if Flash Fill cannot recognise the correct pattern and produces an inaccurate result.
Another way to fill with Flash fill is by using a shortcut like Control + E.
Flash Fill can simply handle the easy task of combining the first and last name, or it can combine the first, middle, and last name.

*Remember that Flash Fill isn't completely perfect. After finding patterns, it fills every cell in the column with the same pattern. Even though Flash Fill will probably function as intended, verifying the outcome is helpful.

4. Combine the first and last names with a full stop.

In Microsoft Excel, you can combine the names using a formula. See the instructions below to find out how to merge the names using a full stop.

The procedure for creating a formula in Excel to combine names via a full stop is as follows:

Open the Microsoft Excel program.
Select the first cell in the column labelled "Full Name."
Enter the formula =First Names Serial number&"."&Last Name Serial Number in the Empty Function Box.
The double quotation area needs a full stop, as I have indicated by using red highlight.
The following formula is ( =A2&","&B2 );.
Select the Full Name cell handle('+') and move it to the end of the column.

You'll notice immediately that a comma has been added between each of your names, and they have all been combined.

5. Power Query

One fantastic tool for extracting and transforming data is Power Query.
This can also be used to easily merge the first and last names and merge columns.
For Power Query to function, your data must be in an Excel Table (or within a Named Range).
I will create an Excel table out of the data collection containing this lesson's first and last names.

Assume the following data collection is yours, and you want to combine the two columns to obtain the complete name.

The procedures to turn the data into an Excel table are as follows:

Pick a cell inside the dataset.
Click the Insert tab.
Select the Table symbol.
Verify that the range in the Create Table dialogue box is accurate.
Select "OK."
The data range would become an Excel table with the above methods.

Let's now see how to use Power Query to merge the first and last names:

Select a cell in any table cell.
Select the Data page.
Select 'From Table/Range' from the Get & Transform Data group. In doing so, the Power Query Editor will open.
Verify that the correct table was chosen in the left pane of the Power Query Editor. One option will be visible within the left pane if you have just one table.
Choose the columns you must combine by clicking on their headers while holding down the Control key.
After doing a right-click, select Merge Columns.
When the Merge Columns dialogue box appears, choose space (from the drop-down) as the delimiter.
Give the newly combined column a name. I'll choose "Full Name."
Select OK. This would delete the current two columns and add a new column with the complete name.
Combined names in the Full name column.
Select the File tab, then press the Close and Load button.
The steps mentioned above would create a new worksheet containing a table with the entire name in one column within the workbook.

To get both the original and new columns with the entire name, choose the Add Column tab on the Power Query Editor, pick the columns, and finally click Merge Columns. This will create a new column containing the entire name.
Power Query is undoubtedly slightly longer than Flash Fill and formula methods.
However, the advantage of this approach is that you can rapidly refresh the query so your resultant data will automatically update if your original data changes.
Furthermore, combining tables and data from several worksheets and workbooks is an everyday use for Power Query. Therefore, it only takes one step if you already have a power query workflow and want to combine the name data.

Next TopicMs Excel Logo

← prev next →