0

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?

John Beasley
  • 2,577
  • 9
  • 43
  • 89
  • If you have access to `LAMBDA()` functionality, you could have a look over [here](https://stackoverflow.com/a/65363987/9758194) where you'll find a generic 'split' function that does the trick along with the xpath expression provided by Scott. If you don't know the specific delimiters, just cram all characters in that you can see that devide substrings. – JvdV Nov 10 '21 at 17:14

1 Answers1

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.

enter image description here

If does not have FILTERXML then we can use:

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

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Testing........ – John Beasley Nov 10 '21 at 16:36
  • No luck... I get the same exact data and text. – John Beasley Nov 10 '21 at 16:37
  • @JohnBeasley then there is something different in your data that is not in what you posted. As you can see both formula that I posted are tested and return the correct value. – Scott Craner Nov 10 '21 at 16:39
  • @JohnBeasley, My guess is that the `,` is not a true `,` but some other character that looks like a `,` – Scott Craner Nov 10 '21 at 16:39
  • The text can be random. I tried to provide a couple of examples, but there are quite a few. The second function you provided works a little better, but it excludes others. Seems like I may have to run a couple of functions in order to get it to work. – John Beasley Nov 10 '21 at 17:05
  • You will need to get them to a common divider. You will need to account for each that can happen. If you do not tell Excel what the divider is, it cannot interpolate. – Scott Craner Nov 10 '21 at 17:08
  • I recommend you create a user function with VBA, and then use it as a formula. Please take a look at this link https://www.extendoffice.com/documents/excel/1272-excel-extract-email-address.html – Mohamad TAGHLOBI Nov 10 '21 at 17:16
  • Do you think if I brought this into an Access database, I could write a query that would extract the email? (unfortunately, Access is the only DB I am allowed to use). – John Beasley Nov 10 '21 at 17:27