How to extract part of a string?

[jmartmem]

Is there a function, or a combination of functions, in MS Access 2007 that will allow me to extract part of a string?

I have a database text field "Email" that contains a person's name and email address enclosed in brackets. Not all field values have a name, but each has an email address contained by brackets. Here are 3 example field values:

John Smith <johnsmith@emai l.com>
<marysmith@emai l.com>
Willard Smith <willardjsmith@ email.com>

What I want is to extract is the email address from between the brackets. The email address is always in the brackets as shown above.

Any advice or suggestions you can offer would be greatly appreciated.

[yarbrough40]

Are you looking for a solution using SQL or VB?

if VB then, not sure if there is an easier way to do this but you could use a combination of the InStr() function and the Substring() function...
something like this:

 
Dim s As String 
s = Text1.Value        'THIS IS THE WHOLE STRING
 
Dim i1 As Integer = InStr(s, "<") 
Dim i2 As Integer = InStr(s, ">")
Dim MyEmail As String = s.Substring(i1, i2 - (i1 + 1))
MsgBox(MyEmail)

[ADezii]

1. Function Definition:
   

   Public Function fExtractEMailAddr(strString As String) As Variant
     If InStr(strString, "<") > 0 And InStr(strString, ">") > 0 Then
       fExtractEMailAddr = Mid$(strString, InStr(strString, "<") + 1, _
                           (InStr(strString, ">") - InStr(strString, "<")) - 1)
     Else
       fExtractEMailAddr = Null
     End If
   End Function

2. Sample Function Calls and Results:
   

   Debug.print fExtractEMailAddr("Willard Smith <[e-mail]>")
   'will yield
   [e-mail]

   Debug.print fExtractEMailAddr("Willard Smith <[e-mail]")
   'will yield (missing '>')
   Null