howto block ads

 [Excel] Help comparing data in two columns I am using Excel 2007. Column A has 1,700 names in this format: 'John Smith' and 'Jane A Doe' Most of the names are in the format of 'Jane A Doe'.
Column B has 3,600 names in the format of 'Jane Doe'. All the names are formatted like this so the 'Jane A Doe' in Column A would be the same as 'Jane Doe' in Column B.
I am looking for a way to strip out the middle initial in Column A, so that I can make a better comparison between the two columns to see if the name in Column B appears in Column A.
Thank you for any assistance.   jaaPremium join:20000613 kudos:2 Reviews: ·Vonage ·Optimum Online
 c1=find(" ",a1) will find the first space. d1=left(a1,c1) will give you the first name with the trailing space. e1=find(" ",a1,d1+1) will give your the second space. f1=mid(a1,e1+1,len(a1)e1) will give you the last name. g1=d1&f1 will give you first and last name, no initial.
For Jane A Doe: c1=5 d1="Jane " e1=8 f1="Doe" g1="Jane Doe"
Column G and now be compared to column B.
You will have to work with it a little to handle no middle initial and other things in the data, but you get the idea.  NOTHING justifies terrorism. We don't negotiate with terrorists. Those that support terrorists are terrorists.  
 cmcasey79 join:20001210 Allen Park, MI kudos:1
2 recommendations  reply to bmantz65
In C1 (or whatever column you want to use to compare to column b), try: =CONCATENATE(IF(ISERR(FIND(" ",A1)),"",LEFT(A1,FIND(" ",A1)1))," ",IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)LEN(SUBSTITUTE(A1," ","")))))))   cmcasey79, BrightHouse RoadRunner Turbo, Farmington Hills, MI    said by cmcasey79:In C1 (or whatever column you want to use to compare to column b), try: =CONCATENATE(IF(ISERR(FIND(" ",A1)),"",LEFT(A1,FIND(" ",A1)1))," ",IF(ISERR(FIND(" ",A1)),"",RIGHT(A1,LEN(A1)FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))))) This did the trick. Thank you cmcasey79!  
