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

[patrok]

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.

 

[mikej336]

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

Uncle Mike

 

[mikej336]

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

 

[mintjulep]

regex.

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

 

[PHV]

Have a look here:

http://msdn.microsoft.com/library/en-us/script56/html/2380d458-3366-402b-996c-9363906a7353.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

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,

_[red][/red]

 

[CautionMP]

patrok,
Here is a concept.

Function to parse the email addresses

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)

 

[patrok]

CautionMP,

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

Cheers,
Patrick.

 

[misterstick]

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. <

 

[PHV]

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.