*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:

=*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:

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:

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:

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:

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)`

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 nanes, you can easily 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. 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 Combine 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:

- 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:

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:

If you are curios to try this and 60+ more time-saving tools included with our Ultimate Suite, you are welcome to download a trial version for Excel 2019 - 2010.

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 17 responses to "How to combine first and last name in Excel"

Thank You!!! This helped

This is so good and thank you for your expertise and sharing with us!!!

superrr

&

thank you

It didn't help me at all

I want to merge first and last name and remove those columns but with this forumulae i can only merge now i can't delete those columns.

Hi Raj,

Here's an easy fix:

- Merge the first and last name using a formula.

- Replace the formulas with values. The detailed instructions can be found here: How to replace formulas with their values in Excel.

- Delete the original columns.

How can I separate four names into three cell, for example, DR. FARUKU ABDULLAHI MOHAMMED, can this names be split into three cells

Hi,

I would suggest making use of the Ablebits ‘Split Names’ and ‘Merge Columns into One’ tools, which is a sort of workaround, of course. Please note that you can change the column order with the help of ‘Column Manager’ offered by ‘Ablebits Ultimate Suite’.

If you feel like working with formulas, you might find it helpful to read the following article first: 'How to separate names in Excel: split first and last name into different columns' by Svetlana Cheusheva.

Name. Joining. Ending. Output

01-01-2019 01-04-2019

In output m o1-01-2019 ko 01-04-2019 Kaise latest?

Please Help me !

How do I concatenate a name in one cell like this:

Donald D Duck

To in return be in one cell, but to look like this:

Duck, Donald D

Thank u so much. It was in deed an awesome practical steps.

if i writing my name RinkuSaini.so how i create a space in this name in one cell.

I need to more know I phone and open in is first put I have another iphone

Very helpful!

Great!

I have been trying to find a solution to a problem for several days and your information finally provided the answer. Thank you!!

I have 3 columns, Firstname Surname Country. Can you CONCAT the 3 columns with the Country displayed in brackets? ie. I would like it to return result as follows:

Donald DUCK (Australia)

With thanks.

Hello!

You can use this formula:

=B1&" "&C1&" ("&D1&")"

Here is the article that may be helpful to you: Excel CONCATENATE function