About Us

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.

wickedchew

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Check the lasta character	Manos	Excel Worksheet Functions	2	April 19th 10 09:35 AM
Check for character in string	dksaluki	Excel Discussion (Misc queries)	3	December 11th 07 12:53 PM
Spell check / character cell limit	Suzanne	Excel Discussion (Misc queries)	2	July 17th 07 08:38 PM
Ã¼ instead of a check mark when I try to copy it from Character Map	Jim 90068	Excel Discussion (Misc queries)	3	February 28th 07 06:54 PM
formula to check Cell value within specific character	TA	Excel Discussion (Misc queries)	2	February 12th 07 05:46 PM

Menu

About Us