|
|
|
Senior Member
|
|
Location: Philippines
Posts: 161
|
|
Quote:
Originally Posted by johnmerlino
Hey all,
I have a formula, which basically concatenates the first name and middle initial to right of ampersand with the last name to left of ampersand if an ampersand exists in cell:
Code:
=IF( ISERROR( SEARCH("&", A1) ), IF( ISERROR( SEARCH(" ", A1) ), A1, LEFT(A1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))-1)),SUBSTITUTE(A1,MID(A1,FIND(",",A1,1)+1,FIND("&",A1,1)-FIND(",",A1,1)),""))
This has an unintended consequence of this type of scenario:
Code:
CORNWALL,CORNWALL,ANNA
Because the calculation thought CORNWALL was first name and ANNA was middle name in this:
CORNWALL,A ROBERT & CORNWALL,ANNA
It stringed together CORNWALL as the last name and CORNWALL as the first name.
Is there a way to check for the following pattern using excel formulas:
[name],[name],
and replace with
[name],
I'm just trying to avoid duplicate last names. Thanks for response. |
Attach a sample of the data.
__________________
Asobi Wa Owari Da
|