by Svetlana Cheusheva, updated on
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.
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.
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:
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:
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:
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)
Whichever formula you opt for, the results are going to be the same:
With different name parts listed in 3 separate columns, here's how you can merge them all into one 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:
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:
Tip. In Excel 2016 - 365, you can also use the CONCAT function to combine names.
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 |
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.
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)
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)
You can also capitalize the first letter in each cell as explained in the above-linked article.
If your goal is to get a list of full names independent of the original columns, or you wish to eliminate the source columns after merging the names, you can easily convert the formulas to values by using the Pates Special command. After that, you are free to delete the original columns containing the name parts.
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 combining names in Excel.
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:
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:
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:
Tip. If you want to keep the original first and last name columns, make sure the Backup this worksheet box is selected.
As the result, the first and last names are merged into one and placed in the left column:
That's how to combine first and last name in Excel. I thank you for reading and hope to see you on our blog again next week!
Combine Names in Excel - examples (.xlsx file)
Ultimate Suite - trial version (.exe file)
Table of contents
