Extracting Email Addresses from Tables

[Melih]

Hi,

We have some tables that have email addresses contained
within one of the text fields. That field has other
content and what we want to do is extract just the email
addresses. The email addresses of course vary in length.
It seems like it would do a search on "@" then go in either
direction until it found a "space". We've tried TRIM, LEN,
INSTR expressions, but we cannot seem to quite get it the
syntax right. The LIKE expression didn't seem it would do
the trick.

We found an expression that could pick out someone's middle
initial KB #286238, but in our case it has to first search
for the "@".

Anyone have a suggestions?

Thanks.

 

[John Spencer (MVP)]

This might be a situation for a custom vba function. TRY the following UNTESTED AIRCODE

Public Function GetEMail(FldIn)
Dim intPos as Integer, strReturn as String

intPos = Instr(1,FldIn & vbNullString,"@")

If intPos = 0 Then
GetEmail = Null
Else
'Get string up to space after the @ sign
strReturn = " " & fldIn & " "
strReturn = Trim(Left(strReturn,Instr(IntPos+1,strReturn," ")))
StrReturn = " " & strReturn
strReturn = TRIM(Mid(StrReturn,InstrRev(strReturn," ",-1,vbTextCompare)))
GetEMail = strReturn
End If

End Function