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)

There is no Error Code it, just Sends Back Not Matched. In Regex101 it shows the grouping(using JavaScript), i expect it should return 1 group — Ryan
– Ryan, Commented Dec 1, 2017 at 10:12
I don't understand your pattern. What are you trying to match? — Tom
– Tom, Commented Dec 1, 2017 at 10:56
@Tom Group1: (Test A1) Group 2: ((III’15)) Group 3: (270) Group 4: (10/12) Group 5: (ABC/DEF) Group 6: (PNR) Group 7:(AVC) — Ryan
– Ryan, Commented Dec 1, 2017 at 11:23
You Regex is not working for me... I tried to make a demo on Regex101, however, it is "ugly". The regex ([\w\s]*)\s*(\([\S\s]+?\))\s*([\w\d]+)\s*([\d|\-|/]+)\s*([\w|/]+)\s*(\w+\s?(?:\d|\.)*)\s*(\w+(?:\s?-\s\w+)?) — danieltakeshi
– danieltakeshi, Commented Dec 1, 2017 at 12:34

Rory · Accepted Answer · 2017-12-01 12:34:05Z

1

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

answered Dec 1, 2017 at 12:34

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

3 Comments

Tom Over a year ago

I can't seem to get it to work. It just returns (Not matched)

Ryan Over a year ago

@Tom It was the Char160, on regex101 they were considered as spaces

Ryan Over a year ago

@Tom Could you Help me out Test Asd(II’05) 300 11/12 RtF/Z XC PNR NKL Test 33 (I’01) PIL 11/12 K NP/IL O IL 90.5 FX - NO I was having trouble grouping (K NP/IL O), it needs to be in 1 group when I introduce a space ([\w]+ ) it detects everything after (RtF/Z XC PNR NKL).

Tom · Accepted Answer · 2017-12-07 13:53:04Z

0

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

edited Dec 7, 2017 at 13:53

answered Dec 1, 2017 at 12:38

Tom

9,9084 gold badges33 silver badges51 bronze badges

2 Comments

Ryan Over a year ago

Could you Help me out Test Asd(II’05) 300 11/12 RtF/Z XC PNR NKL Test 33 (I’01) PIL 11/12 K NP/IL O IL 90.5 FX - NO I was having trouble grouping (K NP/IL O), it needs to be in 1 group when I introduce a space ([\w]+ ) it detects as(RtF/Z XC PNR NKL).

Tom Over a year ago

@Ryan I've updated the code again. It should now detect these lines as well correctly. I've also updated the RegExr link - have a look at it to understand what the pattern is doing

Collectives™ on Stack Overflow

Excel Vba Regular Expression Not Detecting

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related