by 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:
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:
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:
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:
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:
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.
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)
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)
You can also capitalize the first letter in each cell as explained in the above-linked article.
Replace formulas with values and delete the original columns
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.
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:
- For the first entry, type the first and last name in an adjacent column manually.
- Start typing the name in the next row, and Excel will immediately suggest full names for the entire column.
- Press Enter to accept the suggestions. Done!
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:
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:
- Select the two columns of names you want to combine.
- On the Ablebits tab, in the Merge group, click the Merge Cells drop-down arrow, and choose Merge Columns into One:
- 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:
Tip. If you want to keep the original first and last name columns, make sure the Backup this worksheet box is selected.
- Click the Merge button.
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!
Available downloads
Combine Names in Excel - examples (.xlsx file)
Ultimate Suite - trial version (.exe file)
34 comments
I am trying to figure out a way to convert several cells with full first name last name in each cell (e.g. John Doe in cell A1. John Smith in cell A2, etc.) into first name initial full last name (J. Doe in cell A1 and J. Smith in cell A2). What formula would i use and is there a way to make it apply to the full column of cells instead of having to do each one individually?
Did you ever figure this out?
Hi! Determine the position of the first space using the SEARCH function and replace all characters in the first word except the first with "." using the REPLACE function. Try this formula:
=REPLACE(A1,2,SEARCH(" ",A1)-2,".")
Merging first name and last in different column as a full name using formula. While deleting First and last name column why full name column is also deleting. Kindly help me on this ?
Hi! If you combine cell values using a formula, you must not delete the cells that are referenced by the formula. Before deleting, convert formula to values. You may also find this guide helpful: How to merge cells in Excel without losing data.