If Formula help

[STIRRELL]

Hi, I have a great formula that works most of the time to parse out an email address, but.... Sometimes they add their nick name in the field with parens and the formula pulls the nick name instead of the email address . Can you help tweek the formula?


Example:
Works
=MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1)

Aaron Wad - NTCT (aaron.wad@Certify.com)​

returns
aaron.wad@Certify.com

where it breaks:

Aaron ( Jeff) Wad - NTCT (aaron.wad@Certify.com)​

Returns
Jeff

I was not sure how to write this to ignore the () with the nickname.
thanks for your help,
Sharon

 

[RasGhul]

Hi Sharon,

It would be helpful to see more examples to check if solution accommodates all possibilities;

If the " - " is in all names maybe;

=IF(ISNUMBER(SEARCH(" - ",A2)),MID(MID(A2,SEARCH(" - ",A2),LEN(A2)),SEARCH("(",MID(A2,SEARCH(" - ",A2),LEN(A2)))+1,SEARCH(")",MID(A2,SEARCH( " - ",A2),LEN(A2)))-SEARCH("(",MID(A2,SEARCH(" - ",A2),LEN(A2)))-1),"")

 

[Dave Patton]

a quick thought before someone posts a neat solution

T202108b.xlsm
	A	B
1
2	Aaron Wad - NTCT (aaron.wad@Certify.com)	aaron.wad@Certify.com
3	Aaron ( Jeff) Wad - NTCT (aaron.wad@Certify.com)	aaron.wad@Certify.com
4
7b

Cell Formulas
Range	Formula
B2:B3	B2	=SUBSTITUTE(MID(A2,MAX(FIND("~",SUBSTITUTE(A2,"(","~",1)),IFERROR(FIND("~",SUBSTITUTE(A2,"(","~",2)),0))+1,99),")","")

 

[Dave Patton]

or

T202108b.xlsm
	A	B
1
2	Aaron Wad - NTCT (aaron.wad@Certify.com)	aaron.wad@Certify.com
3	Aaron ( Jeff) Wad - NTCT (aaron.wad@Certify.com)	aaron.wad@Certify.com
7b

Cell Formulas
Range	Formula
B2:B3	B2	=SUBSTITUTE(MID(A2,MAX(FIND("(",A2,1),IFERROR(FIND("(",SUBSTITUTE(A2,"(","~",1),1),0))+1,99),")","")

 

[Fluff]

Another option

=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"(",")"),")","</m><m>")&"</m></k>","//m[contains(.,'@')]")