On Sat, 23 Apr 2011 16:03:14 -0700, "Robert Crandal" wrote:

Hi everyone. So I think I finally have a better understanding of
how to use regular expressions in VBA, but I have a more
difficult question.

I am working with strings that basically contain random sets
of character strings (or "tokens") that are separated by any
number of whitespace characters. Here are some example
strings that I might encounter:

* "The age of the dog is 100 years!!!"
* "aa bb cc dd ee 01 10 111 ooo"
* " Here is a sting that contains a total of 13 tokens... got it?"

I am trying to develop a regex pattern string that will let me enumerate
or collect each of the tokens in the string. I would like to use the
Submatches() function to retrieve any "token" in the string by index.

So, using the third string above as an example, I would want
the Submatches() function to return the following:

.Submatches(0) = "Here"
.Submatches(1) = "is"
.Submatches(2)= "a"
.Submatches(3)= "string"
.Submatches(4)= "that"
etc... etc....
.Submatches(12) = "it?"

I hope my question makes sense. I just need help with the
pattern string. So far, the only thing I could think of is the
following:

"(\s+(\S+)\s*)+" ?????

Got any ideas?

Robert Crandal




One other note: Although I know you are trying to become proficient in regular expressions, this same task can be accomplished fairly simply in VBA. For example:

==================
Function SplitString(s As String) As Variant
SplitString = Split(WorksheetFunction.Trim(s))
End Function
======================

SplitString will be a zero-based array containing your collection.