VBA regex and group

Question

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?

match objects have a submatches collection, so you'd loop over that for each match. — Tim Williams
– Tim Williams, Commented Jun 7, 2017 at 3:47

Gurmanjot Singh · Accepted Answer · 2017-06-07 05:05:57Z

21

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

answered Jun 7, 2017 at 5:05

Gurmanjot Singh

10.4k2 gold badges22 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mohammad Alshaikh Over a year ago

Thanks, (for each)sentence should be contains matches instead of match.. and i need to get 1stmatch.1stgroup and 1stmatch.2ndgroup and so on .. in separated fields?

mohammad Alshaikh Over a year ago

please could any one assist on this?

Gurmanjot Singh Over a year ago

I didn't get what you are saying? You have the whole working code now. It is displaying both the groups of each match. Do you want to display the whole match?

Collectives™ on Stack Overflow

VBA regex and group

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related