How to combine first and last names in excel into one column without losing data.
Ampersand sign is a formula solution, with no excel function.
Similar to solution #1, you must write all the cell addresses together with the separator but the formula using an Excel function.
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.
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.
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 🙂
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?
The first case opposite. How to separate first and last name from the full name?
The formula for First Name column
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
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.
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
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.