splitting and rejoining text in cells
 

I have some data that I have pulled back from an SQL server that I need to
match but they clearly do not match in computer terms as A = B.

I have two columns that I need to compare

A B
1 "Fred Bloggs" "Bloggs, Fred"


clearly I can not use =if(A1 = B1,"Yes","No")

There is a slight twist as well, when "Fred Bloggs" was entered they might
be 1 or to spaces between the names and the same goes for "Bloggs, Fred"


Can you advise what function or set of functions I would use?

Many Thanks

Mark

This seems to work;

=SUBSTITUTE(A1," ","")=MID(SUBSTITUTE(B1,"
",""),FIND(",",B1)+1,255)&LEFT(SUBSTITUTE(B1," ",""),FIND(",",B1)-1)



"sh0t2bts" wrote in message
...
I have some data that I have pulled back from an SQL server that I need to
match but they clearly do not match in computer terms as A = B.

I have two columns that I need to compare

A B
1 "Fred Bloggs" "Bloggs, Fred"


clearly I can not use =if(A1 = B1,"Yes","No")

There is a slight twist as well, when "Fred Bloggs" was entered they might
be 1 or to spaces between the names and the same goes for "Bloggs, Fred"


Can you advise what function or set of functions I would use?

Many Thanks

Mark

Try:

=IF(MATCH("*"&LEFT(A1,FIND(" ",A1)-1)&"*",B1,0)*MATCH
("*"&MID(TRIM(A1),FIND(" ",A1)+1,255)
&"*",B1,0),"Yes","No")

HTH
Jason
Atlanta, GA

-----Original Message-----
I have some data that I have pulled back from an SQL
server that I need to
match but they clearly do not match in computer terms as
A = B.

I have two columns that I need to compare

A B
1 "Fred Bloggs" "Bloggs, Fred"


clearly I can not use =if(A1 = B1,"Yes","No")

There is a slight twist as well, when "Fred Bloggs" was
entered they might
be 1 or to spaces between the names and the same goes
for "Bloggs, Fred"


Can you advise what function or set of functions I would
use?

Many Thanks

Mark


.

Excellant this worked a treat.

Thanks :o)


"sh0t2bts" wrote in message
...
I have some data that I have pulled back from an SQL server that I need to
match but they clearly do not match in computer terms as A = B.

I have two columns that I need to compare

A B
1 "Fred Bloggs" "Bloggs, Fred"


clearly I can not use =if(A1 = B1,"Yes","No")

There is a slight twist as well, when "Fred Bloggs" was entered they might
be 1 or to spaces between the names and the same goes for "Bloggs, Fred"


Can you advise what function or set of functions I would use?

Many Thanks

Mark