CODEWITHSUNDEEP
regexvba
mohammad Alshaikh
mohammad Alshaikh

Reputation: 103

VBA regex and group

applying the below regex on below email body:

(pls[a-zA-Z0-9 .*-]*) \(([A-Z 0-9]*)\)

email body:

pls18244a.lam64*fra-pth (PI000581) 
pls18856a.10ge*fra-pth (PI0005AW) 
pls25040a.10ge*fra-pth (IIE0004WK) 
pls27477a.10ge*fra-pth (WL050814) 
pls22099a.stm4*par-pth (PI0005TE)

returns 5 match, with two groups. what is the VBA script to get groups in each match using using incremental variable to copy each match groups in excel row?

Upvotes: 9

Views: 16773

Answers (1)

Gurmanjot Singh
Gurmanjot Singh

Reputation: 10360

Not making any changes to your regular expression pattern. Using the following way, you can iterate through the groups of each match:

str="pls18244a.lam64*fra-pth (PI000581)pls18856a.10ge*fra-pth (PI0005AW)pls25040a.10ge*fra-pth (IIE0004WK)pls27477a.10ge*fra-pth (WL050814)pls22099a.stm4*par-pth (PI0005TE)"
Set objReg = New RegExp
objReg.IgnoreCase=False
objReg.Global=True
objReg.Pattern = "(pls[a-zA-Z0-9 .*-]*) \(([A-Z 0-9]*)\)"
Set objMatches = objReg.Execute(str)
For Each match In objMatches             'The variable match will contain the full match
    a= match.Submatches.Count            'total number of groups in the full match  
    For i=0 To a-1
        MsgBox match.Submatches.Item(i)  'display each group
    Next
Next
Set objReg = Nothing

Upvotes: 21

Related Questions