How to combine first and last name in Excel

This tutorial is going to show you a few different ways to combine names in Excel: formulas, Flash Fill and the Merge Cells tool.

Excel worksheets are often used for storing data about different groups of people - customers, students, employees, and so on. In most situations, first and last names are stored in two separate columns, but occasionally you may need to combine two names in one cell. Luckily, the days of merging anything manually are over. Below you will find some quick tricks to join names in Excel that will save you a lot of boring time.

Excel formula to combine first and last name

Whenever you need to merge first and last names together in one cell, the fastest way is to concatenate two cells by using the ampersand operator (&) or the CONCATENATE function as shown in the below examples.

Formula 1. Combine first and last name in Excel

Let's say, in your worksheet, you have one column for given name and another column for surname and now you want to join these two columns into one.

In the generic form, here are the formulas to combine first and last name in Excel:

=first_name_cell&" "&last_name_cell
CONCATENATE(first_name_cell," ",last_name_cell)

In the first formula, concatenation is done with an ampersand character (&). The second formula relies on the corresponding function (the word "concatenate" is just another way to say "join together"). Please pay attention that in both cases, you insert a space character (" ") in-between to separate the name parts.

With the first name in A2 and the last name in B2, the real-life formulas go as follows:

=A2&" "&B2

=CONCATENATE(A2, " ", B2)

Insert either formula in cell C2 or any other column in the same row, hit Enter, then drag the fill handle to copy the formula down to as many cells as you need. As the result, you will have the first name and last name columns combined into the full name column:
A formula to combine first and last name in Excel

Formula 2. Combine last name and first name with comma

If you are looking to merge names in the Last Name, Fist Name format, use one of the following formulas to join first and last name with comma:

=last_name_cell&", "& first_name_cell
CONCATENATE(last_name_cell,", ",first_name_cell)

The formulas are basically the same as in the previous example, but here we concatenate the names in the reverse order and separate them by a comma and a space (", ").

In the screenshot below, cell C2 contains this formula:

=B2&", "&A2

And cell D2 contains this one:

=CONCATENATE(B2, ", ", A2)

In Excel 2016 and Excel 2019, you can also use the CONCAT function to combine names:

=CONCAT(B2, ", ", A2)

Whichever formula you opt for, the results are going to be the same:
Formula to combine last name and first name with comma

Formula 3. Join first, middle and last name in one cell

With different name parts listed in 3 separate columns, here's how you can merge them all into one cell:

=first_name_cell&" "&middle_name_cell&" "&last_name_cell
CONCATENATE(first_name_cell," ",middle_name_cell," ",last_name_cell)

Technically, you just add one more argument to the already familiar formulas to merge the middle name.

Assuming the first name is in A2, middle name in B2, and last name in C2, the following formulas will work a treat:

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

=CONCATENATE(A2," ",B2," ",C2)

The screenshot below shows the first formula in action:
Combining first, middle and last name in one cell

In situation when column B may or may not contain a middle name, you can handle each case individually, and then combine two formulas into one with the help of an IF statement:

=IF(B2="", A2&" "&C2, A2&" "&B2&" "&C2)

This will prevent the appearance of extra spaces between words in rows where a middle name is missing:
Prevent the appearance of extra spaces in combined names.

Formula 4. Merge first initial and surname

This example shows how to combine two names into one in Excel and convert a full name into a short name.

Generally, you use the LEFT function to extract the first letter of the forename, and then concatenate it with the surname separated by a space character.

With the first name in A2 and the last name in B2, the formula takes the following shape:

=LEFT(A2,1)&" "&B2

or

=CONCATENATE(LEFT(A2,1), " ", B2)

Depending on the desired result, one of the following variations of the above formula may come in handy.

Add a period after the initial:

=LEFT(A2,1)&". "&B2

Merge the initial with the last name without a space:

=LEFT(A2,1)&B2

Combine the initial and last name, and convert the combined name to lowercase:

=LOWER(LEFT(A2,1))&LOWER(B2)

For your convenience, the following table shows all the formulas along with their results:

A B C D E
1 First name Last name Combined Name Formula Description
2 Jane Doe J Doe =LEFT(A2,1)&" "&B2 Initial + Surname separated by a space
3 J. Doe =LEFT(A2,1)&". "&B2 Initial + Surname separated by a period and a space
4 JDoe =LEFT(A2,1)&B2 Initial + Surname without a space
5 jdoe =LOWER(LEFT(A2,1))&LOWER(B2) Initial + Surname in lowercase without a space

Tips and notes on combining names in Excel

As you have just seen, it's very easy to merge first name and last name in Excel with a formula. But if, against all expectations, your formula works imperfectly or does not work at all, the following tips may help you get on the right track.

Trim extra spaces

In case your information is coming from an external database, chances are that the original columns have some trailing spaces invisible to the human eye, but perfectly read by Excel. As the result, extra spaces may appear between the merged names like in the left-hand table below. To eliminate excessive spaces between words to one space character, wrap each cell reference into the TRIM function, and then concatenate. For example:

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

Trim extra spaces and combine names

Capitalize the first letter in each name

If you are working with a personnel roster created by someone else, and that someone is not a very accurate person, some of the names may be written in lowercase and others in uppercase letters. An easy fix is using the PROPER function that forces the first character in each word to uppercase and the rest to lowercase:

=PROPER(A2)&" "&PROPER(B2)

Combine names and capitalize the first letters

Replace formulas with values

If your goal is to get a list of full names independent on the original first and last name columns, convert the formulas to values by using the Pates Special command. The detailed instructions can be found here: How to replace Excel formulas with their values.

To have a closer look at the formulas discussed in the first part of this tutorial, you are welcome to download our sample workbook to Combine Names in Excel.

How to merge first and last name in Excel automatically

When using formulas, the result and the original data are closely connected - any changes made to the original values are immediately reflected in the formula's output. But if you don't expect any updates to the combined names, leverage Excel's Flash Fill capability to automatically fill data based on a pattern.

Here's how you can combine names in a second with Flash Fill:

  1. For the first entry, type the first and last name in an adjacent column manually.
  2. Start typing the name in the next row, and Excel will immediately suggest full names for the entire column.
  3. Press Enter to accept the suggestions. Done!

Merging first and last name in Excel with Flash Fill

The beauty of this method is that Excel perfectly "mimics" your pattern, capitalization and punctuation, so you can have the names joined exactly the way you want. The order of the name parts in the original columns does not matter! Just be sure to type the name in the first cell exactly as you want all the names to appear.

For instance, see how easily you can combine names with comma:
Combining first and last name with comma

How to combine first and last name by merging cells

Another quick way to combine names in Excel is to merge cells containing the name parts. No, I am not talking about the inbuilt Merge feature because it only keeps the value of the upper-left cell. Please meet the Ablebits Merge Cells tool that keeps all your values while merging cells :)

To join first and last name by merging cells, here's what you do:

  1. Select the two columns of names you want to combine.
  2. On the Ablebits tab, in the Merge group, click the Merge Cells drop-down arrow, and choose Merge Columns into One:
    Merge columns into one
  3. The Merge Cells dialog box will show up. You type a space character in the Separate values with box and leave all other options as suggested by default:
    Separating the combined names with a space character

    Tip. If you want to keep the original first and last name columns, make sure the Backup this worksheet box is selected.
  4. Click the Merge button.

As the result, the first and last names are merged into one and placed in the left column:
The first and last names are merged into one.

If you are curios to try this and 60+ more time-saving tools for Excel included with our Ultimate Suite, you are welcome to download a trial version for Excel 2019, 2016, 201 and 2010. If you like it and decide to get a license, don't miss an opportunity to get all the tools with a 15% discount by using this coupon code: AB14-BlogSpo

Thank you for reading and see you next week!

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
November offer: Nov. 5 – Nov. 21