Extract second name from cell containing 2 names

[davistax]

Assume I have two names in cell A1 that are always in the same format:

example: John & Mary Smith

I need a formula that will place "Mary" in A2.

Have been stumbling around with this most of the day & am hitting a wall.

Help is greatly appreciated.

 

[XOR LX]

Hi.

So you mean there are never any middle names or other initials present?

Regards

 

[Rick Rothstein]

Assume I have two names in cell A1 that are always in the same format:

example: John & Mary Smith

I need a formula that will place "Mary" in A2.

Maybe this (it will handle middle names/abbreviations as well as single names with an ampersand)...

=REPLACE(REPLACE(A1,1,FIND("&",A1&"&")+1,""),FIND(" ",REPLACE(A1,1,FIND("&",A1&"&")+1,"")&" "),99,"")

 

[davistax]

That's correct. Always first name of husband & wife, separated by "&", followed by last name.

Hi.

So you mean there are never any middle names or other initials present?

Regards

 

[davistax]

That's perfect! Thanks.

Maybe this (it will handle middle names/abbreviations as well as single names with an ampersand)...

=REPLACE(REPLACE(A1,1,FIND("&",A1&"&")+1,""),FIND(" ",REPLACE(A1,1,FIND("&",A1&"&")+1,"")&" "),99,"")