How to Combine and Separate First and Last Name in Excel
How to Combine First and Last Name in Excel
How to
combine first and last names in excel into one column without losing data.
Solution #1, Using Ampersand Sign
The Formula
=A2&" "&B2
The Result
Ampersand sign is a formula solution, with no excel function.
Solution #2, Using CONCATENATE Function
The Formula
=CONCATENATE(A2," ",B2)
The Result
Similar to solution #1, you must write all the cell addresses together with the separator but the formula using an Excel function.
Solution #3, Using TEXTJOIN Function
The Formula
=TEXTJOIN(" ",TRUE,A2:B2)
The Result
a simpler formula, you only write the separator once, cell addresses are combined in the form of a range address, no need to write the cell address one by one.
For all solutions using a formula, don’t forget to do a Copy – Paste Special – Values for all combined names.
For what?
- You can delete the first, and last name columns and no error occurs
- You can copy the combined name to another place without any problems
Read the following article for a detailed explanation of paste special
Solution #4, Using Flash Fill Menu
Flash Fill is no formula solution. Place the cursor in cell C2. Type first and last name for the first name.
Do the same for the second name, after you type two or three letters, Excel displays all combined first and last name for all names. Press the ENTER button.
Solution #5, Using NOTEPAD
Do a copy for all first and last name then paste in NOTEPAD. There is a space between the two with varying widths, that is a TAB character.
Replace the TAB character to space with the “Replace” menu. Copy the results and paste them again in Excel. You got the full name with no formula 🙂
Which One is the Best Solution?
If you choose to use a formula, my choice is solution #3 using the TEXTJOIN function. Use the range address, and you get a combined first and last names.
For no formula solution, my choice is solution #4 Using the “Flash Fill” menu. If you can’t do it in your Excel version, do it using NOTEPAD, solution #5.
Which of the two is better? A formula solution or no formula solution? I chose to use the “Flash Fill” Menu, no formula solution. What about your choice?
How to Separate First and Last Name in Excel
The first case opposite. How to separate first and last name from the full name?
Solution #6, Using Excel Formula
The formula for First Name column
=LEFT(A2,SEARCH(" ",A2)-1)
The Result
The SEARCH function is looking for the space position that separates the first and last name. The LEFT function takes characters from the first character until the space position, reduced by 1.
The formula for Last Name column
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
The Result
The RIGHT function takes n characters from the right, to find out the n value, use the LEN function to calculate the character length minus the space position.
Solution #7, Using Text to Column Menu
The Text to Column menu is in the “Data” tab, the “Data Tools” group. You can access it using a shortcut /, A, E.
Block range A2:A14. Type /, A, E in sequence. A dialog appears for the text to column
Select delimited then click next.
Excel provides many separators, because of space separate first and last names, then select space and click next.
You can format the separated data according to the conditions. For first and last names all is text. Change them into text, do it one by one. Click Finish.
The full name disappears, change to first and last name
Which One is the Best Solution?
The “Text to Column” menu is my choice, no more questions.
Regardless of the number of words, three words, four words or even more “Text to Column” can separate them easily. If you use the excel formula, it could be your nightmare.
Related Function
Function used in this article
Related Articles
Another articles related to this article
Usefull links
- Microsoft Excel Combine First and Last Name documentation