Excel Vba Regular Expression Not Detecting

Question

I have tried to detect the same on regex101, but when I try to run the excel-VBA code it fails to detect.

I have been trying to detect and group the Following Text:

Test A1 (III’15) 270     10/12  ABC/DEF       PNR       AVC
Test Asd(II’05) 300     11/12  RtF/ZXC      PNR        NKL
Test 33 (I’01) PIL     11/12  KNP/ILO      IL 90.5    FX - NO
Test 4 (IIII’10)   270  11-12/12  JKI/IOP   PNR      RPTD - RPTD

My Pattern: ([\w ]+)\s+([\w()\’\’ ]+)\s+(\w+)\s+([\w/-]+)\s+([\w/+]+)\s+([\w.\s]+)\s+([\w -]+)

My Code:

Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A1:A63")

For Each C In Myrange
    strPattern = "([\w ]+)\s+([\w\(\)\’\’ ]+)\s+(\w+)\s+([\w\/\-]+)\s+([\w\/\+]+)\s+([\w\.\s]+)\s+([\w \-]+)"

    If strPattern <> "" Then
        strInput = C.Value
        'strReplace = "$1"

        With regEx
            .Global = False
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            C.Offset(0, 1) = regEx.Replace(strInput, "$1")
            C.Offset(0, 2) = regEx.Replace(strInput, "$2")
            C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            C.Offset(0, 4) = regEx.Replace(strInput, "$4")
            C.Offset(0, 5) = regEx.Replace(strInput, "$5")
            C.Offset(0, 6) = regEx.Replace(strInput, "$6")
            C.Offset(0, 7) = regEx.Replace(strInput, "$7")
            Else
            C.Offset(0, 1) = "(Not matched)"
        End If
    End If
Next
End Sub

I need to group then as Group1: (Test A1) Group 2: ((III’15)) Group 3: (270) Group 4: (10/12) Group 5: (ABC/DEF) Group 6: (PNR) Group 7:(AVC)

Answer 1

I think your pattern was off, and you also need to set Global = True. This pattern will target each group of your strings. I haven't tested it with the Replace though as not sure what you were trying to achieve there.

RegExr Fiddle

Updated with new pattern after comments:

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String, strInput As String, strReplace As String
    Dim j As Long
    Dim match
    Dim Myrange As Range

    Set Myrange = Range("A1:A63")

    strPattern = "(^.*(?=\())|(?:\().*(?:\))|((\d{2}(\/|\-)){1,2}\d{2})|(([a-z]{3}|[a-z]{1,2}\s[a-z]{1,2})(\/|(?!\/))){2}|((\w{2,4}(\s\-\s|$)){1,2})|((\w{2}\s)\d{1,2}\.\d{1,2}|[a-z]{3})|((?!\)\s+)(([a-z]|[0-9]){3}))"
    If strPattern <> vbNullString Then
        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern
        End With
        For Each c In Myrange
            strInput = c.Value
            If regEx.test(strInput) Then
                j = 1
                For Each match In regEx.Execute(strInput)
                    c.Offset(0, j).Value2 = match
                    j = j + 1
                Next match
            Else
                c.Offset(0, 1) = "(Not matched)"
            End If
        Next
    End If
End Sub

Answer 2

This will work assuming your spaces are real spaces and not character 160 as they are when copied from here:

Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range
Dim matches

Set Myrange = ActiveSheet.Range("A1:A4")

For Each C In Myrange
strPattern = "([\w ]+)\s*(\([\w\’]+\))\s+(\w+)\s+([\w/-]+)\s+([\w/+]+)\s+(\w+\s?[\w.]*)\s+([\w -]+)"
    If strPattern <> "" Then
        strInput = C.Value
        'strReplace = "$1"

        With regEx
            .Global = False
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            C.Offset(0, 1) = matches(0).SubMatches(0)
            C.Offset(0, 2) = matches(0).SubMatches(1)
            C.Offset(0, 3) = matches(0).SubMatches(2)
            C.Offset(0, 4) = "'" & matches(0).SubMatches(3)
            C.Offset(0, 5) = matches(0).SubMatches(4)
            C.Offset(0, 6) = matches(0).SubMatches(5)
            C.Offset(0, 7) = matches(0).SubMatches(6)
            Else
            C.Offset(0, 1) = "(Not matched)"
        End If
    End If
Next
End Sub