CODEWITHSUNDEEP
regexexcelvba
Kantoboy
Kantoboy

Reputation: 25

finding a substring in regex excel / vba

I have very long string with a particular pattern. similar to the following:

AG156 fox is the animal AG156 cat is a pet AG156 stars are in the sky AG156 Roses + red.

There may be line breaks at any point. I want to return words between "AG156" and "red" but only the AG156 immediately before red.

Desired result

"AG156 Roses + red".

How do I this with regex? Currently my expressions returns everything between the first AG156 and red.

Thanks!

Upvotes: 0

Views: 803

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

The Regular expressions used in VBA doesn't support lookbehinds IIRC. So, you can use this pattern (?:.|\n)*(AG156(?:.|\n)*?red). The first (?:.|\n)* needs to be greedy to consume all the other AG156, else making it non-greedy will match everything between the first AG156 to the word red. The second (?:.|\n)*? should be non-greedy to match up to the first occurrence of the word red.

Sub test()

    Dim retVal As String, inputStr As String

    inputStr = "AG156 fox is the animal AG156 cat is a pet AG156 stars are " & _
            "in the sky AG156 Roses + red."

    With New RegExp
        .Pattern = "(?:.|\n)*(AG156(?:.|\n)*?red)"
        .Global = True
        .MultiLine = True
        retVal = .Execute(inputStr)(0).SubMatches(0)
    End With

    Debug.Print retVal
    ' Prints the string: "AG156 Roses + red"

End Sub

If you prefer late-binding your regex object, replace this line:

With New RegExp

with this:

with createobject("VBScript.RegExp")

Upvotes: 1

Related Questions