Extract email address from field

[zssteen]

I had a list of several thousand email addresses I pulled from a number of mailing lists I manage. I planned to migrate them to a new email management system. The problem is that some entries included the recipient's name followed by the email address surrounded in greater than/less than signs (<>).

For example - John Doe <jdoe@domain.com>

I wanted to write a script that would extract just the email address.

After some trial and error I got it working. I am posting it for the benefit of those who do not know Visual Basic and for those who do but don't feel like figuring it out themselves.

Here is the script:
=MID(B2,SEARCH("<",B2)+1,SEARCH(">",B2)-SEARCH("<",B2)-1)

B2 is the field in which the email address with the name lies.

That works very nicely unless there are some fields that contain only the email address. With this script those would return a #VALUE error since the script can't find any ">" signs.

To solve this I just add and IF/THEN statement as follows:
=IF(RIGHT(B4821, 1)=">",MID(B4821,SEARCH("<",B4821)+1,SEARCH(">",B4 821)-SEARCH("<",B4821)-1),B4821)

I also had an issue in that some of the email address-only entries were preceded by a space. To make sure that is trimmed off I use the TRIM command:


=TRIM(IF(RIGHT(B4821, 1)=">",MID(B4821,SEARCH("<",B4821)+1,SEARCH(">",B4 821)-SEARCH("<",B4821)-1),B4821))

Run that and I get what just the email address and nothing else: jdoe@domain.com

--Zev Steen

[Bird_FAT]

Nice post - Thanks Zev!