View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ker_01 ker_01 is offline
external usenet poster
 
Posts: 395
Default VBA Regex syntax problems (XL2003)


I have a long string, in the format

BAR garbage text FOO desired string 1 BAR desired string 2 BAR desired
string 3 BAR desired string 4 BAR (etc).

What I'd like to extract is:
desired string 1
desired string 2
desired string 3
desired string 4

I'm trying variations of re.Pattern = "FOO|BAR [\s\S]*?BAR "
but I'm having some problems.

(1) The first returned string returns
BAR garbage text FOO desired string 1 BAR
where I'd prefer to only start with
FOO desired string 1 BAR ... or better,
desired string 1

I can work around that manually, but I welcome any suggestions on how to fix
it.

(2) Each search 'consumes' the terminating string "BAR", causing it to skip
every other string, giving me:
BAR garbage text FOO desired string 1 BAR
BAR desired string 3 BAR
BAR desired string 5 BAR

How do I force it to re-use the terminating string as the start string of
the next part of the search?

(3) I'm not sure if I need it or not, but I tried using re.multiline = True
just to be cautious, and I get a compile error "Method or data member not
found". I do have a reference to Microsoft VBScript Regular Expressions 1.0.
Do I need a different reference to enable multiline? Again, the strings still
seem to be coming across ok (including across multiple lines), I'm just
surprised I don't need (and in fact, can't set) the multiline parameter.

Thank you,
Keith