Sometimes you receive a file that has the full name in a single column but you want to split it up into two columns - First Name and Last Name.
You start with this:
You want to end up this:
TIP: The actual spreadsheet shown in this example (in Excel format) is attached to this article.
1. Copy the spreadsheet into a new name.
This will allow you to go back to the original spreadsheet in case you make a mistake in the formulas, or this process.
2. Working on that new copy of the spreadsheet, insert 4 columns right after the Full Name column.
3. Add this formula to cell B2 to pull the first name:
=MID(A2,FIND(",",A2,1)+2,99)
This will look at cell A2 with the full name and use the MID text function to pull from 2 characters (+2) after it finds the "," to the end of the name - which is the First Name
NOTE: If the full name is not in column "A", but in column D, you would have to change the formula to use "D2" instead of "A2"
4. Add this formula to cell C2 fo pull the last name:
=MID(A2,1,FIND(",",A2)-1)
5. Copy those formulas all the way down to the bottom of the list of Full Names
6. Now, highlight both columns by clicking the top row to select them (B and C). Select to copy them. Then PASTE SPECIAL > VALUES to the two blank columns next to them.
7. Just to be safe... make a backup copy of your spreadsheet at this point with "-with-formulas" at the end of your file name. (Save As...) So you can come back to this step after all the work you just did.
8. After making a backup copy of your sheet, then double-check that the cells you just pasted have the actual names, and not the formulas. (If you see formulas, re-paste and select Paste Special - Values)
9. The final step is to remove all the columns with the Full Name and the Formulas. Highlight those three columns (in this case A, B, C) and Delete them.
10. Finally, you should be left with the First and Last name columns the names in them like this:
IMPORTANT: If you see #REF! or #VALUE! in the cells instead of First and Last name, then you forgot to Paste Special > Values and need to go back to the backup copy of your sheet you created in Step 7 that with the file name that ends "-with-formulas" and try again.
Comments