Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2009, 08:57 AM
zssteen zssteen is offline Extract email address from field Windows Vista Extract email address from field Office 2007
Novice
Extract email address from field
 
Join Date: Jun 2009
Posts: 1
zssteen is on a distinguished road
Default Extract email address from field

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
Reply With Quote
  #2  
Old 06-19-2009, 02:32 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Extract email address from field Extract email address from field Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

Nice post - Thanks Zev!
Reply With Quote
Reply

Tags
email, script, visualbasic

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract email address from field Same email address on 2 or 3 computers Bob Newton Outlook 2 08-20-2010 03:34 PM
Extract email address from field How can I fill in form I received in email that used underlines instead of text field Gail L. Word 2 05-17-2009 12:38 PM
EMAIL address change warrencassell Word 8 12-10-2008 09:22 PM
Auto entry of email address (who I'm sending to) Miket Outlook 5 12-10-2008 04:41 PM
Hiding the email address John Word 0 01-10-2008 04:39 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft