Regex capturing multiple distinct occurence of string pattern

Question

I am trying to capture all the product codes in the text. They sometimes are multiple. I am able to capture just one. I guess its more of the excel vba question rather than the regular expression as I have the str pattern.

 Sub regexp()

Dim regEx As New regexp
Dim strPattern As String
Dim Myrange As Range
Dim LastRow As Integer


LastRow = ActiveSheet.Cells(Rows.Count, "W").End(xlUp).Row
Set Myrange = ActiveSheet.Range("W4:W" & LastRow)

For Each c In Myrange


strPattern = "(?:\s[ABCDabcd][0-9][A-Za-z0-9]{3}\s|\s[ABCDabcd][0-9oO][0-9oO)][0-9][0-9A-Za-z]\s|\s[ABCDabcd][0-9oO][0-9A-Za-z)][0-9A-Za-z][0-9]\s|[^A-Za-z0-9][ABCDabcd]\s[0-9][A-Z0-9a-z]{3}\s)"
    If strPattern <> "" Then
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        Set matches = regEx.Execute(c.Value)
        On Error Resume Next
        c.Offset(0, 7).Value = matches.Item(0)
    End If
Next c
End Sub

How do I change the code to capture distinct multiple string pattern in the text. This code currently only captures the first one. I want to capture all seperated by a comma. Eg- First I had problems with the product b0067. Then issues were resolved. Later I had issues with this D0887 . This was followed by C689W, D 7890 OUTPUT- b0067,D0887,C689W,D 7890 I want to trim these as the spaces were considered at the end or beginning.

Answer 1

First, I suggest enhancing the regex to

strPattern = "(?:\s[abcd](?:[0-9][a-z0-9]{3}|[0-9o][0-9o)][0-9][0-9a-z]|[0-9o][0-9a-z)][0-9a-z][0-9])|[^a-z0-9][abcd]\s[0-9][0-9a-z]{3})(?!\S)"

See the regex demo. Make sure you set .IgnoreCase = True. This regex will check if there is a whitespace or end of string after your match with a (?!\S) lookahead, so A0ABC and B0ABC will be extracted from "1 A0ABC B0ABC 3". The first \s can be changed to (^|\s) to also match the strings at the start of a string.

Then, after running Execute(), iterate over the matches:

 Set matches = regEx.Execute(c.Value)
 For Each m In matches
    Cells(x, y) = m.Value
 Next

This is a whole fixed sub that will print the matches as a comma separated value to the cell you specified:

Dim regEx As New regexp
Dim strPattern As String
Dim Myrange As Range
Dim LastRow As Long, cnt As Long

LastRow = ActiveSheet.Cells(Rows.Count, "W").End(xlUp).Row
Set Myrange = ActiveSheet.Range("W4:W" & LastRow)
strPattern = "(?:\s[abcd](?:[0-9][a-z0-9]{3}|[0-9o][0-9o)][0-9][0-9a-z]|[0-9o][0-9a-z)][0-9a-z][0-9])|[^a-z0-9][abcd]\s[0-9][0-9a-z]{3})(?!\S)"

If strPattern <> "" Then
  With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = strPattern
  End With
  cnt = 0
  For Each c In Myrange
    Set matches = regEx.Execute(c.Value)
    For Each m In matches
        c.Offset(0, 7).Value = c.offset(0, 7).Value + m.Value
        cnt = cnt + 1
        If cnt < matches.Count Then c.offset(0, 7) = c.offset(0, 7) & ","
    Next
  Next c
End If

Note the place where the RegExp is initialized - outside the loop.