extract email from column

Question

Trying to extract the email from a column in an excel sheet using the following formula:

=TRIM(MID(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",40)),
 FIND(REPT("@",COLUMNS($A1:A1)),SUBSTITUTE
 (SUBSTITUTE(" "&$A1&" "," ",REPT(" ",40)),"@",REPT("@",COLUMNS($A1:A1)),COLUMNS($A1:A1)))-40,80)) 

The formula works for the most part, however some of the records still include other text.

For example, one of the records looks like this:

**TEST** John Beasley,jbeasley@usa.com,7575551212

After using the above formula, I get the following results:

Beasley,jbeasley@usa.com,7575551212

Here's another example:

USA-USA/J Beasley/jbeasley@usa.com/757-555-1212

Results after using formula:

Beasley/jbeasley@usa.com/757-555-1212

Here is an example of when the formula works (before):

**US AMA TES DATA** John Beasley, jbeasley@usa.com

Which yields the following results:

jbeasley@usa.com

So the formula works, but it also does not work.

How can I write the formula so that it extracts everything except the actual email address?

Answer 1

If one has it, use FILTERXML:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"/",","),",","</s><s>")&"</s></t>","//s[contains(.,'@')]")

FILTERXML was introduced in Excel 2013 and only on PC not Mac.

If does not have FILTERXML then we can use:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"/",","),",",REPT(" ",999)),FIND("@",SUBSTITUTE(SUBSTITUTE(A1,"/",","),",",REPT(" ",999)))-500,999))