how-to block ads
|
|
Uniqs:
392 |
Share Topic
 |
 |
|
|
|
| [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:2000-06-13
kudos:2
 ·Optimum Online
 ·Vonage
| 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:2000-12-10
Farmington, MI
kudos:1 | 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! | |
|
