Extract Email ID From A List Of Sentences

[dunndealpr]

Hey all. I was in this forum a year or so ago when Rick Rothstein saved my life with a VBA code that extracts email addresses from text in Excel files. See http://www.excelfox.com/forum/f2/vba-to-extract-email-address-from-text-1092/index3.html.

Well, it's been working swimmingly until now. I've found The Cursed File. It's a file of data which Rick's code cannot extract data from no matter what format I save it in.

See the attached file. I created it from scratch, applied Rick's code, and in the top line I pasted a line of data from The Cursed File. As you can see, the code does not extract the email.
Underneath, I created test data. As you can see, the code works.
On the final line, I pasted in some similar data from another file. Code works.

What gives? I've noticed that when I delete about two-thirds of the text from the Cursed Data line in D1, the code works. But that's no fix at all.


Thanks much in advance.

[snb]

An alternative:


Function F_snb(c00)
F_snb = Trim(Replace(Join(Filter(Split("|~" & Join(Filter(Split(c00), "@"), "|~"), "|"), "~@", False)), "~", ""))
End Function

[dunndealpr]

Hey SNB, thanks for your input. I tried running your VBA instead of Rick's on the test xl attached as well as the original Cursed File but all it gives me is #NAME?

Did you try your VBA on my test file?

[snb]

Yes I tested it on your file.

Do you know where to store a UDF and how to call a UDF ?

[dunndealpr]

I don't. I'm not far past Excel novice status. Can you explain?

[Excel Fox]

dunndealpr,

Just copy SNB's code above, and paste it in the same module after Rick's code (after the last line of course).

The instead of =GetEMailAddress(D1), use =F_snb(D1)

Of course you can rename the name of the function to anything you want if needed.

[dunndealpr]

wow! it worked! thank you.

1. why didn't Rick's code alone work on these particular contacts? Rick's code has worked time and time again for me. Even when I pasted the text into a textpad then back into a brand new excel file they still wouldn't work, even alongside other similar data that Rick's code was handling just fine. It confounds me.

2. Will this combination of Rick's code and the new code work across the board for all such data? Or just in this instance where Rick's code isn't doing the trick for whatever weird reason?

Thanks guys.

[snb]

It was rather meant as a replacement of Rick's code.

[dunndealpr]

so that code works on the document alone for you? without Rick's code first?

[snb]

Yes, it does. It's the only code you need.

[dunndealpr]

Not over here.. For me it only works when placed after Rick's.

[snb]

You can't be serious.