Using a query to extract email addresses

[epsilon]

Hi there,

I am a relative newcomer to Access, and am trying to work out if there is a way to isolate email addresses in a line of text, and delete all the text around them.

I have basically got a column which contains blocks of text and I want to extract the email addresses that are embedded within them. Does anyone know if this is possible by running queries?

Thank you in advance for any help!

Dan

 

[yellow]

How many lines of text are you talking about? How many email addresses are involved?

It should be possible. As an email address does not contain any spaces. And it must contain an @.

Therefore you could:

1) Use InStr to find the position of each @ sign
2) From there you can move character by character both left and right until you find a space
3) Use the info obtained from (2) and use Mid to extract the email address

You're going to need to code this rather than running it in a query.

 

[mresann]

Actually, the problem is more complicated than that. In many cases, an email address may be part of a string or html field that don't have spaces in front of and behind the email address string. It is quite common for email addresses to be in html format, such as <myname@mymailaddress.com>, or in strings with other addresses, such as myname@mymailaddress.com;yourname@yourmeailaddress.net;hisname@hisaddress.org.

If you know FOR SURE that there are spaces in front of and following the address, AND that the @ sign is used ONLY in an email address, you can use this in a single line (even query field):

Mid(strText, InStrRev(strText, " ", InStr(stremail, "@")) + 1, InStr(InStr(strText, "@"), strText, " ") - InStrRev(strText, " ", InStr(strText, "@")))

NOTE: this uses the InStrRev function, which may not be available on Access 97 VBA (I'm not sure).

 

[epsilon]

Thanks very much for your help so far guys!

Sorry to be completely dumb, but I have only really used Access before for data sorting etc, and have never used the query function. Where would I need to input that line of code?

At the moment all the emails either have spaces at each end of them, or alternatively are surounded with hyperlink brackets <>. I can use find/replace to change all hyperlink brackets to spaces however, so all addresses will then have a space at each end of it.

Thanks again
Dan

 

[yellow]

Design a new blank query.

Add the relevant table.

In the field column add the query provided by mresann.

IE: Mid(strText, InStrRev(strText, " ", InStr(stremail, "@")) + 1, InStr(InStr(strText, "@"), strText, " ") - InStrRev(strText, " ", InStr(strText, "@")))

You need to replace strText with the field name in your table that contains the email address. You also need to replace the stremail in the above with your field name in your table that contains the email address.

 

[emilis]

would someone mind breaking down how this query statement works? i'm trying to understand the logic behind it and i'm not getting it.

thanks in advance!

emilis