CODEWITHSUNDEEP
regexexcelcsvregex-lookaroundsvba
hale
hale

Reputation: 45

VBA regex everything after words

I have the sentence "lorem ipsum dolor marker words blah blah blah"

I need only the words after (but not including) 'marker words'. 'Marker words' are the only words in the sentence that remain the same.

I managed to find (?<=\bmarker words\s)(\w+) which not only doesn't work in vba, but it also returns only one word after the marker words ex: blah but I'm not sure how to extend this to the rest of the string (if I were to work on it outside of Excel). My research says that vba doesn't even support lookbehinds at all so I am stuck.

Upvotes: 2

Views: 2509

Answers (2)

Saleem
Saleem

Reputation: 8988

Try following regex:

"(?:marker words)(.*?)$"

Your string should be in first group.

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627083

VBA VBScript regex does not support lookbehind construct. You can only rely on capturing:

\bmarker words\s+(.*)

See the regex demo

The result is in the SubMatches collection.

The \bmarker words\s+ subpattern matches the whole words marker words followed with 1 or more whitespace(s) and (.*) subpattern matches zero or more characters other than a newline and places it into a capture group, a memory buffer, where the value can be further retrieved from with language specific methods.

VBA code:

Sub CaptureSubstring()
  Dim str As String
  Dim objMatches As Object
  str = "lorem ipsum dolor marker words blah blah blah"
  Set objRegExp = CreateObject("VBScript.RegExp")     ' Declare the RegExp object
  objRegExp.Pattern = "\bmarker words\s+(.*)"         ' Set pattern
  Set objMatches = objRegExp.Execute(str)             ' Execute the regex match
  If objMatches.Count <> 0 Then                       ' Check the result
    Debug.Print objMatches.Item(0).SubMatches.Item(0) ' Print Match 1, Submatch 1
    ' > blah blah blah
  End If
End Sub

Upvotes: 2

Related Questions