How do I compare two different lists of names in Microsoft Excel?

You can use the data text to columns function as someone suggested but before you do it use this formula

=PROPER(TRIM(A1))

this will make you names start with a capitol and have only one space between names. Then copy and pastes special- values only back to A column to get rid of formula. Then after you make all names into different columns you will have 3 columns with middle names and last names mixed in the 2nd column and last names only in 3rd column when a middle name was found. To correct this create a 4th column for middle names and put this formula in it.

=IF(ISBLANK(C1),"",B1)

and fith column for last names and put this in it.

=IF(ISBLANK(C1),B1,C1)

This will copy data based on having a third column which you will only have if you had a middle name.

Once more paste special to get rid of formulas and make into a table and move last name into A column for vlookup to be easier to use.

I decided to once more start making videos and made the one below to better illustrate this. My blog will later update this also.

Excel Comparing Two Lists

I actually just did this earlier this morning. First, you want to separate the names into different columns.Then you want to filter for the names that have middle initials, then "fix" them so they don't have middle initials. Once you "fix" the names by getting rid of the middle initials, you have a normal [FIRST NAME] [LAST NAME] name. If you follow those steps for both lists that you're comparing, you'll be able to compare "clean" names without the occasional middle initials. My explanation covers the basics, but I got all the details from this site: http://www.modernsaver.com/remove-middle-initials-...

Enter the following three formulas in separate columns to parse the data then concatenate it back to [first name] [last name]. Assume your data is in Column A

=LEFT(A1,SEARCH(" ",A1,1))

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

=+B1&" "&C1

Note if your display is showing SUBSTITUTE(A… "

it should be showing SUBSTITUTE(A1," "

For some reason this was appearing on my display