Complex Update Query for Email Addresses

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

We just imported the global address book from our Outlook Exchange Server.
One of the fields we need and want is the user's email address. However, for
reasons unknown to me, in addition to the actual email address, the email
field has a lot of superfluous info . Here's an example [this is not a real
email address].

Taylor, Jonathan A.([email protected])

We need to extract the email address which is always located inside of the
parentheses[almost 5,000 records].

In other words, we want to get rid of the left parentheses and everything to
the left of it. We also want to get rid of the right parentheses. If I stated
that correctly, that would leave us with only the email address.

Any suggestions?

Robert
 
You can use the InStr function to determine where the first open parenthesis
is, then use it again to determine where the closing parenthesis after that
is. What happens, though, if there are parentheses in the name for any other
reason?

Function GetEMail(InputString As String) As String
Dim intOpen As Integer
Dim intClose As Integer

intOpen = InStr(InputString, "(")
If intOpen > 0 Then
intClose = InStr(intOpen + 1, InputString, ")")
If intClose > 0 Then
GetEMail = Mid(InputString, intOpen + 1, _
intClose - intOpen - 1)
End If
End If

End Function
 
Hello Douglas:

Thanks for the prompt attention to my question. I'm not quite sure how that
function operates or where and how to use it.

However, as for your other question, there is only one purpose of the
parentheses. I reviewed the data and figured out that every field is the
same. It has the following:

1. Lastname
2. Comma
3. space
4. Firstname
5. space
6. Middle Initial
7. period

They must use a function to then build the email address. However, I don't
understand why they put the result in the same field surrounded by
parentheses. Why didn't they simply create a separate field just for the
email address?

Anyway, now I have to figure out how to use your function. I'm guessing it's
used in an Update Query.

Robert
 
Back
Top