Learn how to separate full names in Excel spreadsheets. Take a column with a first, middle, and last name and create three columns with each name section in it. Sometimes when importing information, full names can end up in just one column making it difficult to organize names alphabetically or otherwise. The tutorial of splitting full names into multiple cells takes this process and makes it simpler so you can apply it to your own Excel file.
Open the downloaded example and you will see that column A has a list of full names. Your goal is to take a name and put it into three columns, one for the first name, one for the middle initial, and one for the last name.
Start by selecting cell B6 and enter this formula:
Hit enter and cell B6 will now say “Jessica”.
Next is the middle initial. Select cell C6 and put in this formula:
=MID(A6,FIND(” “,A6)+1,FIND(” “,A6,FIND(” “,A6)+1)-FIND(” “,A6)-1)
Now cell C6 says “M.”. Move onto D6 for the last name and enter:
=TRIM(MID(A6,FIND(” “,LOWER(A6),FIND(” “,LOWER(A6),1)+1)+1,LEN(A6)-FIND(” “,LOWER(A6),1)+1))
Cell D6 is now filled with “Livingston”, completing your separation of a full name into a better organized system.
Instead of typing in the formulas for each name, simply drag the already-entered cell down the column and each name will be separated automatically. This will save you from having to spend time entering additional information.
When you are using these formulas on your own Excel sheet, all you need to change is the “A6” in each formula to the cell number that you are retrieving the name from. Everything else can remain the same.
Want to learn more about Excel spreadsheets? Check out our guides for beginners, intermediate users, and advanced Excel users here.
Check out this offer while you wait!