Excel Name List Correction

So recently I responded to a forum question on how to search a name field.   The problem is you can’t search a mixed one column name field, but how do you get the messed up entries into individual cells without retyping your data.  I was amazed that so many people used long formulas instead of the built in Text to columns method so I created this toutorial.   The video above best illustrates my method.

Correct your data 1st.

Formula : 


This formula is a combination of =proper(a1)  and =trim(a1)  by placing it in an unused cell in row 1.  It will trim out all extra spaces and also capitalise the 1st word of each part of proper name.  Copy and paste it down to auto change formula to a2 a3…  Once corrected copy and Paste Special Value only to override original data.  Once done delete the formula area.  It was for one time clean up use. 

Use Text to Columns.

Text to columns is found in the data tab.  You need to choose delimited text and separator is space.  

Use Formulas to seperate middle and Last name.



At this point you will notice column C has some blanks so you can use this to separate the middle and last name from the b column.  Use the 1st in column d for middle names and 2nd one above in column e for last names.  Cut and paste special as a Value.  To get the corrected data back in the proper columns then delete the formula columns.

Move data to Last name, First. Middle

Data is in name order and needs to be switched to search able order (last name 1st).  To do this cut last name column and insert cut cells on the other side of 1st name.

Now make it into a Table

A table has data integrity and can be sorted at whim.  It sorts by any column but keeps 1st last and middle in the same row.  A table is more useful if create headers so insert a blank row above data and label as last, first and middle.  You now can highlight all data and choose insert table.  Check my table has headers so it separates them.

Reformat for better look. 

To make it look better move the table and create fill for better  look.