For text items that are difficult to parse, regular expressions can come in handy. For this example, I put together a function that parses email addresses from string text. For simplicity and reference-sake, I adopted an email regex search pattern from: http://www.regular-expressions.info/email.html.
Not all use cases are created equally, re-purpose my example as needed. Also, please comment with questions/comments/improvements.
Regular expression search for email address in VBA
Public Sub filterByRegex()
''''''''''''''''''''''''''''''''''''''''''''''
'BEFORE USE, ADD REFERENCE TO:
'Microsoft VBScript Regular Expressions 5.5
''''''''''''''''''''''''''''''''''''''''''''''
Dim RegEx As New RegExp
Dim mCol As MatchCollection
Dim mItem As Variant
Dim iInput As String
iInput = "This is a test sampleemail@example.com to " _
& "see if the email test@example.com addresses can be found"
With RegEx
'pattern adopted from practical implementation of RFC 5322:
'http://www.regular-expressions.info/email.html
.Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@" _
& "(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
''''''''''''''
' Global:
' True = Match All occurrences in search string
' False = Match only the first occurrence in search string
''''''''''''''
.Global = True
End With
Set mCol = RegEx.Execute(iInput)
For Each mItem In mCol
'Item output here, debug.print for demo purposes
Debug.Print mItem
Next mItem
Set RegEx = Nothing
Set mCol = Nothing
End Sub
absolutely brilliant! This solved my problem and made my day!
Succinct. Just the way I like my code examples! 🙂
Took me almost no time at all to adapt to my own needs.
Thank you!