Extracting email from text in Memo field

[Michael McGivern]

Need to extract email address from a memo field. I have a table of
approx. 5,000 records. Contained in this table is a memo field, with
various bits of data, which in varying positions contains an email
address. The only way to identify the email string in this memo field
is that it contains the "@" charachter, and the entire email has a
space preceding it. Therefore I have to come with a query that will
extract a text string that contains the charachter "@", or rather, all
charachters on each side of this "@", after a preceding space.

Any help greatly appreciated.
Michael McGivern
(e-mail address removed)

 

[Jeff Boyce]

Michael

No query, sorry. Queries don't, as far as I know, offer a way to iterate
through all the characters in a field and find only those bounded by spaces
and containing a "@". What you described might be amenable to setting up a
wildcard pattern, but I suspect would only return the entire field.

I'd suggest you write a procedure that steps through each character in your
field, adds it to a temporary variable, tests for a space, starts over if
there's a space AND if what's in the variable does NOT contain "@", but
returns the variable if it does.

Good luck!

Jeff Boyce
<Access MVP>

 

[Sidney Linkers]

Hi Michael,

Just create a calculated field in your query as below:

Email: fnGetEmailAddresses([YourMemoFieldNameWithPossiblyEmailAddresses] &
"")

....and you will get in every row 0 or more emailaddresses seperated by a ";
" depending on the contents of the Memofield (Appending an empty string to
your Memofield is necessary because null fields would generate an error in
the call to the function fnGetEmailAddress()).

For this to work you need to include the two functions below in your
databaseapplication.

---Put code below in a module-------------------------------------------
'This function just receives a textbuffer and searches for the first
occurence of the passed delimiter. It cuts it from the textbuffer and
returns the cutoff text.
Public Function fnParsing(strString As String, strDelimiter As String) As
String
Dim intPos As Integer
intPos = InStr(1, strString, strDelimiter, 1)
If intPos > 0 Then
fnParsing = Mid(strString, 1, intPos - 1)
strString = Mid(strString, intPos + Len(strDelimiter))
Else
fnParsing = strString
strString = ""
End If
End Function

'This function loops through the Memo by parsing every word, checks if it
contains a "@", if so then builds-up a emailaddress. It repeats this until
nothing is left to examine in the Memo. 'It finally returns the build-up
emailaddress.
Public Function fnGetEmailAddresses(ByVal strMemo As String) As String
Dim strParsed As String
fnGetEmailAddresses = ""
strMemo = Trim(strMemo & "")
Do While Len(strMemo) > 0
strParsed = Trim(fnParsing(strMemo, " "))
If InStr(1, strParsed, "@", 1) > 0 Then
fnGetEmailAddresses = fnGetEmailAddresses & IIf(fnGetEmailAddresses
<> "", "; ", "") & strParsed
End If
Loop
End Function
------------------------------------------------

I tested it and it works! However you will pay a performance penalty,
because every memofield gets examined and looped through if it contains any
text. You could ofcourse optimize the function a bit by testing for a "@"
occurence, before looking any further in the Memo. Another way is to write a
function that looks for "@" and then cuts-off the leftpart where a space is
found at the rightside and then parses the rightpart until a space occurence
and glue the emailaddress together. Then repeat the whole proces with the
Memo left-over. It should be faster.

Good luck!

Sid.