splitting and rejoining text in cells

[sh0t2bts]

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

 

[Dave R.]

This seems to work;

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

 

[Jason Morin]

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

 

[sh0t2bts]

Excellant this worked a treat.

Thanks )