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)

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.

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

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:

  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.

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)

Table of contents

34 comments

  1. 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?

  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 ?

  3. hey, i want to combine multiple middle names together as one middle name. can you help?

    eg : John Mathey joy chacko

    result : first cell = John, Second cell = Mathey joy, third cell = Chacko

    • Hi!
      If I understand correctly, you can find useful information in this article: How to separate names in Excel. Try these formulas:

      =LEFT(A2,SEARCH(" ",A2)-1)
      =MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ",SUBSTITUTE(A2," ","#",2),SEARCH(" ",A2)+1) - SEARCH(" ",A2)-1)
      =RIGHT(A2,LEN(A2)-SEARCH(" ",SUBSTITUTE(A2," ","#",2), SEARCH(" ",A2,1)+1))

      This should solve your task.

      • Sir Alex,

        thank you, the formula works for a name with particularly 4 syllables on it. i am looking for a more general formula for all type of names..
        the formula must take all the names irrespective on the numbers in the mid area to MID name, and first one to the First Name and only last to the Last name.\\thank you for the support.

  4. I have the following

    First name (1) Second name (1) System login (2)

    Charles Kingsfield

    I need to make the formula for system login 2 that shows the name as charles.kingsfield all lower case separates by a dot

    Thank you

  5. Hi

    I have two columns 'name' and 'surname' I then have a third column 'initial'

    what formula do I use to get the initials of the name and surname?

    I am out of ideas I have tried everything please help

    • Hi!
      To extract the first character from text, use the LEFT function

      =LEFT(A1,1)&LEFT(B1,1)

      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  6. why does excel add the @ in formula to combine first and last names.

    For example I used the following formula; =[LAST] & ", " & [FIRST]. When I entered that a pop up window advised to enter as =[@LAST] & ", " & [@FIRST].

    When I declined entering Excels option, all the cells gave a SPILL error. When I chose the Excel formula option given it worked. I was wondering why putting the @ made a difference.

  7. My first name column always ends up as 0, so if trying to merge first name Victoria and last name Harding, it will merge to one colum but with this as the data: 0 Harding. Help anyone?

  8. I love you guys

  9. 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.

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

  11. Great!

  12. Very helpful!

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

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

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

  16. 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

  17. 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 !

  18. 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.

  19. 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.

  20. superrr
    &
    thank you

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

  22. Thank You!!! This helped

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)