Menu
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?
538
To start using RegEx in VBA, you need to select and activate the RegEx object reference library from the references list. Start by going to the VBA IDE, select Tools –> References, then select Microsoft VBScript Regular Expressions 5.5, then click OK.
Like is an operator in VBA. A comparison operator compares a given string as an argument in a set of strings and matches the pattern. If the pattern matches, the result obtained is “True,” and if the pattern does not match, then the result obtained is “False.” It is an inbuilt operator in VBA.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
