String Search

[Steve]

Hi does anyone know how I could do the following:

I need to search within the cells in Column A and B and output the
result in Column C where the data matches. So in the example below C
would be as shown

A B C

Mulhern, Paul P Mulhern Muhern
Jones, Daniel D Jones Jones
Gibbs, Graham G Gibbs Gibbs
Fenton, David D Jones (would be blank as no match)


As an aside is there any way to convert the data in B to match that in
A where the Surname and Initial in B is the same as the Surname and
first letter of firstname in A


Thanks for your help

Regards



Steve

 

[Ron Rosenfeld]

Hi does anyone know how I could do the following:

I need to search within the cells in Column A and B and output the
result in Column C where the data matches. So in the example below C
would be as shown

A B C

Mulhern, Paul P Mulhern Muhern
Jones, Daniel D Jones Jones
Gibbs, Graham G Gibbs Gibbs
Fenton, David D Jones (would be blank as no match)

=IF(LEFT(TRIM(MID(A1,FIND(",",A1)+1,99)),1)&" "&LEFT(A1,FIND(",",A1)-1)=B1,MID(B1,FIND(" ",B1)+1,99),"")

As an aside is there any way to convert the data in B to match that in
A where the Surname and Initial in B is the same as the Surname and
first letter of firstname in A

That, of course, would be a part of the above formula:

=LEFT(TRIM(MID(A1,FIND(",",A1)+1,99)),1)&" "&LEFT(A1,FIND(",",A1)-1)

 

[Ron Rosenfeld]

here's another suggestion:
=IF(COUNT(FIND(MID(B1&" "&B1,FIND(" ",B1)+1,LEN(B1)-2),A1)),LEFT(A1,FIND(",",A1)-1),"")

This seems to match only the last name. I would have thought the OP would want to match both the last name and the initial of the first name.

 

[Claus Busch]

Hi Ron,

Am Fri, 26 Jul 2013 06:17:42 -0400 schrieb Ron Rosenfeld:

This seems to match only the last name. I would have thought the OP would want to match both the last name and the initial of the first name.

you are right. To match both is the safer way.


Regards
Claus B.