I want to Use grouping with Regular Expressions in EXCEL 2010 and VBA to get the selected parts of a string. I wrote the following function which implements a Get Regular expression (see FUNCTION below).

1. Given a string "123 ABC 308" and a pattern = " \d+". If I specify occurrence n = 0 it returns the first occurrence "123". Likewise if I specify occurrence n = 1 it returns the second occurrence "308". This works fine.

2. I want to use the group feature to return the username and host separately (i.e., alice-b or google.com). I cannot figure out how to do this!

The group feature of a regular expression allows you to pick out parts of the matching text. Given the string "purple alice-b@google.com monkey dishwasher". Suppose I want to extract the username and host separately. To do this, I add parenthesis ( ) around the username and host in the pattern, like this: ([\w.-]+)@([\w.-]+)'. In this case, the parenthesis establish logical "groups" inside of the match text. However, if I set n = 0 or 1 it returns the entire pattern match "alice-b@google.com". How do I use groupings specifically in VBA for EXCEL to do this. I am not so much interested in this particular example, but want to know how to use groupings inside an EXCEL VBA function.

---------------------------------------------------------------------
FUNCTION

Function RegGet1()
'Initialize
Dim str As String
Dim pattern As String
Dim n As Integer
n = 0
str = "purple alice-b@google.com monkey dishwasher"
pattern = "([\w.-]+)@([\w.-]+)"
'str = "123 ABC 308"
'pattern = "\d+"
'Define RegEx Object
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.multiline = False
.IgnoreCase = False
.pattern = pattern
' Get RegEx result
out = .Execute(str)(n)
Debug.Print out
End With
End Function
---------------------------------------------------------------------
Regards,Jeffrey Pattavina
jspattavina@comcast.net