Learn how to split full names in Excel spreadsheets. When you have a worksheet with a column of full names, or have imported a document that puts full names in just one column, you may need to split them into different columns for better organization. This guide will show you how to take one column with full names and turn it into three columns for first, middle, and last names.
Download the example to learn.
Open your example and you will see A5 to A10 has full names listed. Columns B, C, and D have the names split up. Let’s start with just the first name.
Select cell B6 and enter this formula:
=TRIM(LEFT(A6,FIND(” “,LOWER(A6),1)))
After hitting enter, the cell will be populated with “Jessica”. The A6 in the formula shows Excel where to retrieve the first name from.
Now, choose cell C6 and use this formula for the middle name:
=MID(A6,FIND(” “,A6)+1,FIND(” “,A6,FIND(” “,A6)+1)-FIND(” “,A6)-1)
Now C6 will show “M.”. Again, in the formula, A6 indicates where Excel will retrieve the middle name from.
Finally to get the last name listed, go to cell D6 and enter:
=TRIM(MID(A6,FIND(” “,LOWER(A6),FIND(” “,LOWER(A6),1)+1)+1,LEN(A6)-FIND(” “,LOWER(A6),1)+1))
Now cell D6 will show “Livingston”. You now have three columns with split up names that you can sort alphabetically or create ranges with.
When you are applying this formula to your own Excel spreadsheet, all you need to change is the reference cell (A6). The rest can stay the same to work.
Tip: Instead of entering the formula for each cell, drag the corner of the cell with the formula already applied and pull it down the column. It will automatically apply the formula to every name in the row.
Related Templates:
- Remove Middle Initial in Excel
- Get First Name from Full Name
- Get Initials from Names in Excel
- Count Cells in Excel Formula
- How to Combine Two Names in Excel
View this offer while you wait!