By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,828 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,828 IT Pros & Developers. It's quick & easy.

How to extract part of a string?

P: 87
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@email.com>
<marysmith@email.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.
Apr 2 '10 #1
Share this Question
Share on Google+
2 Replies


yarbrough40
100+
P: 320
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim s As String 
  3. s = Text1.Value        'THIS IS THE WHOLE STRING
  4.  
  5. Dim i1 As Integer = InStr(s, "<") 
  6. Dim i2 As Integer = InStr(s, ">")
  7. Dim MyEmail As String = s.Substring(i1, i2 - (i1 + 1))
  8. MsgBox(MyEmail)
Apr 2 '10 #2

ADezii
Expert 5K+
P: 8,636
  1. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fExtractEMailAddr(strString As String) As Variant
    2.   If InStr(strString, "<") > 0 And InStr(strString, ">") > 0 Then
    3.     fExtractEMailAddr = Mid$(strString, InStr(strString, "<") + 1, _
    4.                         (InStr(strString, ">") - InStr(strString, "<")) - 1)
    5.   Else
    6.     fExtractEMailAddr = Null
    7.   End If
    8. End Function
  2. Sample Function Calls and Results:
    Expand|Select|Wrap|Line Numbers
    1. Debug.print fExtractEMailAddr("Willard Smith <willardjsmith@email.com>")
    2. 'will yield
    3. willardjsmith@email.com
    Expand|Select|Wrap|Line Numbers
    1. Debug.print fExtractEMailAddr("Willard Smith <willardjsmith@email.com")
    2. 'will yield (missing '>')
    3. Null
Apr 2 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.