Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Access: Extract Email address from cell containing Free Text 1

Status
Not open for further replies.

patrok

Technical User
Mar 23, 2007
5
US
Hi guys!

Hope you can help me with this one:
- I have a huge MS Access table.
- One of the columns is free text; let's call it "FreeTextColumn".
- Each cell in FreeTextcolumn is free text, and contains either 0 or 1 email address.
- If there is 1 email address, I need to extract it from the free text and copy it to the adjacent cell, in the "EmailColumn").
- the email string ("name@domain.xxx") will always start on a new line and that line will only contain the email address.

For example, if a cell in the FreeTextColumn looks like this:
"Mr Pink
46 years old
likes chocolate
[some other random amount of comments]
pink@panther.com"
Then the adjacent cell in "EmailColumn" should look like this:
"pink@panther.com"

Anyone has an idea how I can do this ?

Maybe thanks!!!
Patrick.
 
Is this a one time thing or something you need to do on a regurlar basis...

Uncle Mike

 
For time sake, I am going to assume this is a one time thing.

I have never processed a memo field one line at a time. I have never heard of it being done. So..

If it were me I think I would write a quick program to copy the contents of each field to text file and name the file by what every your record id number is. (I assuming here that carrige return and line feed would be preserved from a memo to a text file)

Then write a program to process each file, reading each file line by line, looking for @ symbol. If you find @ put that line back into a table using the file name as your record id.

Then use a query to match back up your table to the original.

Uncle Mike
 
regex.

Regretfully, my knowledge of them extends little further than knowing that they exist.
 




Hi,

If I were doing it, I'd use the Split function on " " to find individual "words" and then look for @ in each word.

Skip,

[glasses] [red][/red]
[tongue]
 
patrok,
Here is a concept.

Function to parse the email addresses

Code:
Public Function ExtractEmail(FreeText As String) As String
Dim intOrdinal As Integer
Dim strBuffer() As String

'test if there are multiple email addresses
If Len(FreeText) - Len(Replace(FreeText, "@", "")) = 0 Then
  'Add a place holder so you know if no address was found
  ExtractEmail = "Null"
  Exit Function
End If

'there is at least one address so pull it out
strBuffer = Split(Replace(FreeText, vbCrLf, " "), " ")
For intOrdinal = 0 To UBound(strBuffer)
  If InStr(1, strBuffer(intOrdinal), "@") > 0 Then
    'If there is more than one email address output in a format
    'that you could use to send an email from MS Outlook
    If Len(ExtractEmail) = 0 Then
      ExtractEmail = strBuffer(intOrdinal)
    Else
      ExtractEmail = ExtractEmail & ";" & strBuffer(intOrdinal)
    End If
  End If
Next intOrdinal
End Function

Update Query

[tt]UPDATE tblEmailInText SET tblEmailInText.EmailColumn = ExtractEmail([FreeTextColumn]);[/tt]

Table output after Update query

[tt]FreeTextColumn EmailColumn
"Mr Pink pink@panther.com
46 years old
likes chocolate
pink@panther.com"
"Mr Pink pink@panther.com
46 years old
likes chocolate
pink@panther.com"
"Mr Pink pink@panther.com;pink@panther.com
46 years old
likes chocolate
pink@panther.com
pink@panther.com"
"Mr Pink Null
46 years old
likes chocolate
pinkpanther.com pink"[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
 
CautionMP,

I can't thank you enough!!! :) It's more than I could ask!

Cheers,
Patrick.
 
two things:

1. if you've got a lot of text, Len(FreeText) - Len(Replace(FreeText, "@", "")) = 0 will be really slow.
why not use InStr(FreeText, "@") = 0?

2. the code assumes that Split() returns a zero-based array. assumptions are bad, use intOrdinal = LBound(strBuffer) To UBound(strBuffer) instead.

hth,


mr s. <;)

 
the code assumes that Split() returns a zero-based array. assumptions are bad
The VBA help clearly informs the person taking the time to consult it that the Split function returns a zero based one dimensional array.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top