There are a few different ways of accomplishing this task. If you’re just splitting a short list of names with no suffixes, then text-to-column is probably your best bet.
Life is never that easy though, right? Let’s say you’ve been handed a list of names from one database and you need to import it into another one that needs the name to be two fields only. You try text-to-column just to find that you have a whole extra column for Jr. or III and you don’t want them separated out that far. Well, in comes flash fill to the rescue! You just have to type out a couple of names so excel knows how to handle them and flash fill will see the pattern and complete the column.
Create your new columns. Right click on column B at the top and either hit “I” on the keyboard or select Insert from the menu. Do this again for your second column.
Look through your list. Find one of the names that have more than just first and last name. Fill out the two columns how you’d like the name to appear. Make sure to also choose a name with only first and last name so excel knows how to handle those as well. In this example, I’ve filled in B2, C2, B3 and C3.
Select B4 or really any one of the empty cells in column B.
Go to the Data tab at the top and select Flash Fill from the Data Tools area.
Do the same for column C. Select your cell and choose Flash Fill from the Data tab.
You should now have two complete columns. Now, depending on how extensive your list is, you may want to scroll down and spot any issues. If there had been a Mr. John Brown Sr. for instance, that would confuse excel and give mixed results. If the names on your list are that varied, flash fill may not be the best tool for the job.