find and extract email address from string

[teel73]

Hi Access Forum,

Is there a easy way for access to look in a string and find an email address?

Basically I have 1 field that may include an email address in the value of that field. I want to extract the email addresses and put them into 1 field. The problem is the field is formatted differently in the records.

For example, you may see in 1 field: "Dawson Morton - don@gasp.net"
or you may see it like this "Dawson Morton;don@gasp.net"

Is there a way for access to detect the email address?

 

[plog]

Kind of. There are string functions to extract a substring (Mid() http://www.techonthenet.com/access/functions/string/mid.php), find the position of specific characters in a string (InStr() http://www.techonthenet.com/access/functions/string/instr.php); but you need to explain to the functions how to look for the data you want.

In english, can you generically explain how you would extract out the email address from all your strings? Based, on the examples you have provided, this is how I would do it:

From the first non-space character after the first non-alpha and non-space character in the main string start extracting data until the end of the string.

That process will work for the two examples you provided. Will it work for all of them? If not, you need to find cases where that will fail and revise the definition to account for those. After you have it in english you can begin translating it to code that does what you want.