ExcelBanter - Any way to check for duplicate character patterns in a cell?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Any way to check for duplicate character patterns in a cell? (https://www.excelbanter.com/excel-discussion-misc-queries/266313-any-way-check-duplicate-character-patterns-cell.html)

Any way to check for duplicate character patterns in a cell?

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.

Quote:

Originally Posted by johnmerlino (Post 960064)

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

Attach a sample of the data.