J-Walk & Associates, Inc.

A Function To Extract Email Addresses Posted

I was looking through some old Excel files, and found one from 2002. Apparently, six years ago I had a need to extract emails addresses from text strings. I don't remember it at all. Maybe I was contemplating a new career as a spammer?

In case someone else needs to do this, I posted the VBA function code as a tip: Extracting An Email Address From Text.

I'm sure it's not perfect, but it's probably reliable enough for most uses. The main limitation is that it returns only the first email address in the text. I considered modifying it so it returns all of the email addresses. But then it gets a lot more complicated because the function would need to return an array. So I scrapped that idea.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Dominic. Comment posted 03 September, 2008 9:11am

   Thanks for this - had a need and was going to write my own, now I don't have to.
   Followed the link above to your tips pages and just want to point out that several times you referred to the "@" as an ampersand. This is an ampersand : & The @ sign is actually known as a commat (contraction of "Commercial at" sign).
2. By John Walkenbach. Comment posted 03 September, 2008 9:52am

   Glad you find it helpful, Dominic.
   
   You know, I think I have some type of mental block with the ampersand at sign. I've made the same mistake more than once in my books, and the editors failed to correct it. Thanks for pointing it out.
3. By Malcolm. Comment posted 09 September, 2008 1:49pm

   Hi John,
   
   Regular Expressions (Regex) are a much better way to do this type of thing. This workbook as some good examples, including the email address one:
   
   http://www.vbaexpress.com/kb/getarticle.php?kb_id=68
   
   If I had known about Regex earlier, I could probably have saved 6 months of my life which I've spent writing nasty string searching code
   
   cheers
   
   Malcolm
4. By John Walkenbach. Comment posted 09 September, 2008 3:09pm

   Good point, Malcolm. I never really got into Regex, but I probably should.
   
   For me, the time spent learning that Greek-like Regex would probably cancel out any time saved by using it. And, I generally prefer to use native Excel functionality rather than rely on a reference being available.
5. By malcolm. Comment posted 10 September, 2008 3:44am

   Good points.
   
   But there's none more zealous than a convert - I once spent many weeks writing in VBA what was essentially a simple text parser + pattern matching. Half-way through my brother suggested that it would be a lot, lot easier to use Regex. I had a quick look in Excel help and decided that there was no such thing in VBA, and slightly relieved that I didn't have to learn something new (and with a funny name to boot) I kept plugging away on my master creation.
   
   I'm not sure I've ever written a Regex pattern from scratch. Generally Google writes them for me. This is a good site:
   
   http://www.regular-expressions.info/
   
   Good tutorials, examples and a reference.
   
   Keep up the good work John.
   
   cheers
   
   Malcolm