How to combine two columns in Excel first name last name

A large amount of data in Excel is sorted into many columns to keep it organized and classified. Coming to personal data, more often than not you will find that peoples' names are also split into two (or more) columns, keeping the first name and last name separate. While this helps sort and filter, which is frequently performed with the last name, this tutorial is for the times when you need to combine the first name and surname to a full name.

We have some easy methods and tips that do not involve manually typing all the full names out (phew!). We're going to leave the clerical work to Excel and go through the formulas, functions, and tips that would join the text of two or more cells. Let's see how to add the Mouse to Minnie and Duck to Donald.

Combine First and Last Name

With the aim of combining the first and last names or combining the contents of two columns, we will use the following example as our base for this tutorial:

We will populate column E, combining the contents of columns B and C, using a few different methods that are formulas, functions, and features. Let's begin.

Using the '&' Operator

The '&' operator is also known as the concatenation operator. We will merge the first and last names in our example by using the '&' operator in such a formula:

=B3&" "&C3

What we are doing here is taking the first name from cell B3 and the last name from cell C3. We are joining them with "&" but if we simply join them in this way =B3&C3 or even this way =B3 & C3, we will end up with a full name with no space character in between e.g. the result of this formula would be AbbeyBrooks.

To make sure that there's a space character in the combined name, we split the '&' operator with B3 and C3 and add a space character with double quotes in between. Here are the results of applying and dragging this formula from E3 to E12.

Looks alright to us! More than two text strings will also be combined in the same way e.g., if there also was a middle name, we will add it in the same way with the & operator and a space character.

We just saw how to combine names with a formula. Now let's see the use of functions.

Using the CONCAT Function

The CONCAT function combines a list or range of text strings. It makes a perfect fit for this job so let's put it to work. The formula we will use to combine names in our case example is:

=CONCAT(B3," ",C3)

Let's read this. We have our target cells fed to the function which are B3 and C3. If we merely feed the cells in the function, we will have AbbeyBrooks as the result. That is why we need to enter a space character enclosed in double quotes for the results to turn out like this:

Success! More than two text strings will also be combined in the same way e.g., if there also was a middle name, we will add it in the same way by referring to the cell with the middle name and adding a space character. But CONCAT can also be used differently as it accepts ranges.

Next, we will look at the CONCATENATE function but after CONCAT's introduction in Excel 2019, Microsoft suggests that we use the CONCAT function. Microsoft intends for CONCAT to replace CONCATENATE (considering the intimidating length of the name, perhaps?) because CONCAT also works for ranges while CONCATENATE doesn't. Accepting ranges is a plus point about the CONCAT function and we will talk more about it towards the end of this guide.

Using the CONCATENATE Function

The CONCATENATE function joins several text strings into one text string. For how we are using the CONCAT function for most of this tutorial, the CONCATENATE function can make an exact replacement. Let us demonstrate using our case example.

We have used CONCAT above as =CONCAT(B3," ",C3) and the only thing we will change in this formula is the name of the function from CONCAT to CONCATENATE:

=CONCATENATE(B3," ",C3)

You will find the outcome no different from what we have in the previous segment with CONCAT:

More than two text strings will also be combined in the same way e.g., if there also was a middle name, we will add it in the same way by referring to the cell with the middle name and adding a space character.

So why's good old CONCATENATE getting replaced with CONCAT when they both do the same thing? Firstly, CONCATENATE is an oldie and is still compatible with pre-Excel 2019. Secondly, it doesn't completely do what CONCAT does; CONCATENATE does not support ranges and each cell needs to be entered individually in the formula to be combined. Until now, this doesn't seem like a problem since we are already entering the cells individually to insert a space character in between. But if you scroll to the end, we will show you the use of CONCAT for ranges.

Using the TEXTJOIN Function

The TEXTJOIN function joins a list or range of text strings using a delimiter. A delimiter is a separator between the texts (space character, comma, etc.). This works just like the CONCAT function but accepts the delimiter as an argument in the function. Let's see this with our case example.

Here's the formula we have used:

=TEXTJOIN(" ",TRUE,B3,C3)

The first argument is the delimiter. As we want to separate the names with a space character in between, we have added a space character enclosed in double-quotes as the delimiter. "TRUE" (or "1") is a confirmation to ignore empty cells. B3 and C3 are the texts (names in our case) that we want to join and following are the results:

For joining a pair of texts, all 3 functions detailed above work similarly so choosing a function becomes a matter of preference.

Recommended Reading: How to Concatenate in Excel

Using Excel's Flash Fill Feature

Excel will automatically fill data if it detects a pattern in manually filled data through Flash Fill. This copycat feature picks up on a pattern by second time the data is entered. At the beginning of this guide, we know we said no to manual typing but this method has taken our fancy for good reason. Let's see what's so fanciful about Flash Fill, applying it to an example:

No formula, no function. Just get typing!

• Select cell D3. Type the full name "Abbey Brooks". Press Enter. The selection automatically shifts to the next cell in the column.
• Start typing "Anika". By now, Flash Fill will have recognized your pattern and will display the suggestion.

• Hit Enter. The suggestion will be confirmed by hitting Enter, filling the entire column with the suggested pattern:

Note how the Flash Fill feature is very convenient and accurately follows the pattern provided. Here, you won't have to think of adjusting conditions and formats within a formula and can leave Flash Fill to follow in your wake. While you note this, also note some of our interesting findings of Flash Fill.

Recommended Reading: Excel Flash Fill Feature – Everything You Need To Know

Some Characteristics of Flash Fill

• Flash Fill will not work if there are any empty columns within the dataset. The columns must be aligned together for Flash Fill to detect the pattern.
• If you are not able to get Flash Fill to work, check the settings in File tab > Options in the left panel > Advanced in the left pane of the launched Excel Options dialog box > Editing options. Make sure the Automatically Flash Fill checkbox is ticked.

• Flash Fill can be manually added from the Flash Fill button in the ribbon menu.
• Type the first name manually, press Enter. Go to Data tab > Data Tools section > Flash Fill button. While this will also work, you will miss the satisfaction of seeing the magical appearance of Flash Fill.

• Flash Fill only activates while typing the second fill. If you perform any other task after typing the first fill, Flash Fill will not activate while typing the next fill; there has to be a continuous string of action for Flash Fill to pick up the pattern. This is, however, not such a fretful matter as you can always type another two fills to activate Flash Fill or you can select Flash Fill manually from the ribbon menu.
• Take note of the sentence case in the data set of our example. Some of the initials of the names are in lowercase and some in uppercase. For the first instance, the first name Abbey has an uppercase initial and the last name brooks has a lowercase initial. When we typed the full name Abbey Brooks, Flash Fill picked up on this pattern:

• • First Name – typed as it is, no change.
  • Add space character after First Name.
  • Capitalize initial of the Last Name.

Therefore, we can see in the results that Flash Fill has capitalized the initials of all the last names even though there were some last names in the dataset that did not have capital initials. This is due to the pattern detected by Flash Fill.

As for the first names, Abbey already had a capital initial in the dataset, and Flash Fill detected that no changes are to be made ahead in first names. That is why the first names have been copied by Flash Fill as they are.

If there are any such disparities in your dataset, a helpful trick here would be to change the first row in the dataset to accommodate all the shortcomings (if possible) present throughout the dataset.

For our example, we should change Abbey in cell B3 to abbey. This will make Flash Fill pick up this pattern:

• Capitalize initial of First Name.
• Add space character after First Name.
• Capitalize initial of Last Name.

Now Flash Fill will also capitalize the initials of the first names:

This gets the job done but takes a good amount of manual work. Let's see how to do this much faster.

Capitalize Initial Character of First and Last Name

A super-easy way to attend to capitalizing initials is the PROPER function which can be applied to either the dataset or the resultant column. It would be a good idea to apply the PROPER function along with the concatenation functions that we saw in the above sections.

The final formulas would be:

=PROPER(B3&" "&C3)
=PROPER(CONCAT(B3," ",C3))
=PROPER(CONCATENATE(B3," ",C3))
=PROPER(TEXTJOIN(" ",TRUE,B3,C3))

Now the result looks much better.

Up until now, we saw how to combine first and last names in the format "FirstName LastName". Let's go into combining names in different formats.

Combine Last Name, First Name

Now we will work on combining first and last names in this format: "Last Name, First Name" (with a comma) from the same dataset used in our case example.

The formulas and functions used in the above segments will be used in the same way. We will only switch the positions of the cell references (last name selected first, first name selected last) and add a comma before the space character.

Using '&' Operator

The formula we will apply using the '&' operator:

=C3&", "&B3

Here we have selected the last name first and then the first name. We have joined them the '&' operator and the separator between the names is a comma and a space character ", ". The results of applying this formula are below:

Using CONCAT Function

The formula we will use for applying the CONCAT function:

=CONCAT(C3,", ",B3)

Here we have selected the last name first and then the first name. The separator between the names is a comma and a space character ", ". The results of applying the CONCAT function are below:

Using CONCATENATE Function

The formula we will use for applying the CONCATENATE function:

=CONCATENATE(C3,", ",B3)

Here we have selected the last name first and then the first name. The separator between the names is a comma and a space character ", ". The results of applying the CONCATENATE function are below:

Using Flash Fill Feature

• Manually type the first fill as "Brooks, Abbey".
• Press Enter.

• Begin typing the second fill, the Flash Fill suggestion will appear.
• Press Enter to flash-fill the rest of the names.

All these methods have been discussed in greater detail in the sections above. 

Combining First, Middle & Last Names

As we have explained in the segments above, adding the middle name would be no different than adding the first and last names but if we are to take the TEXTJOIN functions range-accepting abilities into account, things might get a bit easier.

The dataset below is our case example with the addition of middle names:

We already know that the TEXTJOIN function joins a list or range of text strings using a delimiter. So we could easily set the delimiter as " "(space) and select the range containing the first, middle, and last name as:

=TEXTJOIN(" ",TRUE,B3:D3)

Doing this saves our time and efforts as instead of adding the cell references one by one, we are using the range straightaway.

But wait for a second, the CONCAT function also accepts cell ranges. Can't we do the same with CONCAT?

If we were to straight away apply the formula =CONCAT(B3:D3) the resultant combination of names would contain no space characters. Nevertheless, with some adjustments (like adding a space after First, Middle, and Last name columns) we can get it to work. But it is nowhere near the flexibility and ease that the TEXTJOIN function offers.

The options are plentiful; it comes down to what you are comfortable with.

Combining First Name, Initials of Middle Name & Last Name

Wile E Coyote, Mary J Blige, Samuel L Jackson – Now that's the format we are going to work with. While your quickest and easiest bet to achieve this still rests with Flash Fill, what is a guide at all if it's just giving one option? Here we say hello to the LEFT function which returns the specified number of initial characters from a text string. We only need to extract the initial letter for the names so this is what our formula for extracting the initial will be:

=LEFT(C3,1)

The LEFT function picks out the initial from the middle name Lynn in cell C3. 1 indicates the number of initials we require LEFT to extract. The result is L. This is of course not limited to the middle name. The LEFT function will also work for a format like "A Brooks", extracting the initial from the first name.

Now, this function can be merged with any of the functions or formulas used to combine names and all of them will deliver the same result.

=B3&" "&LEFT(C3,1)&" "&D3
=CONCAT(B3," ",LEFT(C3,1)," ",D3)
=CONCATENATE(B3," ",LEFT(C3,1)," ",D3)
=TEXTJOIN(" ",TRUE,B3,LEFT(C3,1),D3)

Here are how the results pan out:

If you aim to add a period after the initial (Abbey L. Brooks), enter the separator in the formula as period then space character ". "

And at last, we come to a close. Firstly, we would like to remind you again that Flash Fill maybe your best friend in the making but we also hope to have given you a deep understanding of the other options that can be used to combine names and ultimately text strings. Lastly, we will be back to serve you with another slice of Excel; be at the ready!