Loop through and match string with wildcards

Question

I am trying to loop through cells in column A and find a specific string of text. The strings of text are like this:

tag:(1001)[EX==]
tag:(1002)[EX==]
tag:(1003)[EX==]
etc...

I want to use wildcards like this:

"tag:(1###)[EX??]"

Where the 3 ###'s are any 3 numbers and the 2 ?? are any 2 single characters.

However, I also want the [EX??] to be in an array like this:

ArrTagSuffix = Array("[EX??]", "[IN??]", "[EXLW]")

So... the wild card string will look something like this:

"tag:(1###)" & ArrTagSuffix

In addition, I want the "1" in "tag:(1###)" to be something like "i" so that I can index it like this:

"tag:(" & i & "###)" & ArrTagSuffix

My first problem is this format with the wildcards doesn't seem to be working. My second problem revolves around trying to figure out the looping/searching.

Basically, assuming "i" can be 1 To 6 and ArrTagSuffix will have 3 different strings. There can ultimately be 18 different combinations of "i's" and ArrTagSuffix. I want to search column A... if it finds no matches... do nothing... but if it finds a match... copy all matching cells onto a new sheet. But I need it to copy it to the same cell as it was found... So... for example... If a match is found in cell A23... it needs to be copied to cell A23 of the new sheet.

So... for example... If Sheet1... Column A has:

Blank cell
Blank cell
tag:(1001)[EX==]
Blank cell
tag:(1002)[EX==]
tag:(1003)[EX==]
Blank cell
tag:(3001)[EX==]
tag:(3002)[EX==]
tag:(3003)[EX==]
tag:(6001)[IN==]
Blank cell
tag:(6002)[IN==]
tag:(6003)[IN==]
tag:(1001)[EXLW]
Blank cell
tag:(1002)[EXLW]
tag:(1003)[EXLW]

The program would find 4 matches:

"1" and an "[EX??]"
"3" and an "[EX??]"
"6" and an "[IN??]"
"1" and an "[EXLW]"

So... it would copy the matches of column A to the appropriate cells of 4 new sheets.

Originally, I had a simpler task where i only needed to find the word "tag" and I had this code which worked:

With ActiveSheet
    Set criteriarange = Range("A1:A" & LShtRow)
        For Each criteriacell In criteriarange
            If Not criteriacell.Value Like "tag:*" Then
                criteriacell.ClearContents
            End If
        Next criteriacell
End With

So I began modifying this to try to accomplish this task... but like I said above... I wasn't even able to get the wildcard portion to work correctly... let alone the loops... Here is where I am at:

With ActiveSheet
    Set criteriarange = Range("A1:A" & LShtRow)
        For Each criteriacell In criteriarange
            If Not criteriacell.Value Like "tag:(" & i & "###)" & ArrTagSuffix Then
                criteriacell.ClearContents
            End If
        Next criteriacell
End With

Immediately I realized that it didn't know what to do with the "i" So I changed the "i" to a "1" temporarily to see if I got any matches:

With ActiveSheet
    Set criteriarange = Range("A1:A" & LShtRow)
        For Each criteriacell In criteriarange
            If Not criteriacell.Value Like "tag:(" & "1" & "###)" & ArrTagSuffix Then
                criteriacell.ClearContents
            End If
        Next criteriacell
End With

but I didn't which indicated to me that my format for the wild cards was not correct. To really simplify things... I tried:

With ActiveSheet
    Set criteriarange = Range("A1:A" & LShtRow)
        For Each criteriacell In criteriarange
            If Not criteriacell.Value Like "tag:(1###)" & ArrTagSuffix Then
                criteriacell.ClearContents
            End If
        Next criteriacell
End With

Still doesn't work and I tried this:

With ActiveSheet
    Set criteriarange = Range("A1:A" & LShtRow)
        For Each criteriacell In criteriarange
            If Not criteriacell.Value Like "tag:(1???)" & ArrTagSuffix Then
                criteriacell.ClearContents
            End If
        Next criteriacell
End With

Still doesn't work... and by doesn't work, I mean it clears the contents of the cells even if they match the format I am searching for.

I also just tried changing the array to: Array("[EX==]", "[IN==]", "[EXLW]") but that didn't work either. I thought maybe the problem was trying to incorporate wildcards in the array. — XCELLGUY, Aug 20 '19 at 16:24
Yes. https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops it's a way to tell the computer to look for patterns instead of perfect matches. Such as if I would ask you to look for `four digits a dash two digits a dash and two more digits` instead of asking you to find a date. Because a date is something we as humans can see, but computers can't. That is why regular expressions is a good tool — Andreas, Aug 20 '19 at 16:28
I don't have time or a computer to write an answer on at the moment, but the pattern you should use is `"tag:(1\d{3})[EX\w{2}]"` — Andreas, Aug 20 '19 at 16:41
No... and yes... I got it working... using "like" but the rest of it (the loops) are ugly... I would like suggestions for making it more elegant. — XCELLGUY, Aug 21 '19 at 05:53

score 0 · Accepted Answer · answered Aug 21 '19 at 06:24

As I commented yesterday Regex is a good tool for this.

First you need to add references to Microsoft VBScript Regular Expressions in the tools menu.

Then add this code:

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

    strPattern = "tag:\((\d)\d{3}\)\[(\w{2}.{2})\]"

    Set Myrange = ThisWorkbook.Sheets("Sheet1").Range("A1:A18")
    With regEx
        .Global = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If strPattern <> "" Then
        For Each c In Myrange
            strInput = c.Value
            If regEx.Test(strInput) Then
                Set matches = regEx.Execute(strInput)
                c.Offset(0, 1).Value = matches.Item(0).SubMatches.Item(0)
                c.Offset(0, 2).Value = matches.Item(0).SubMatches.Item(1)
            End If
        Next c
    End If
End Sub

The code will loop through A1:A18 and look for the pattern, if it matches it will output the first digit in column B and the code between [] in column C.

Then if you only want the unique items, then use the excel function remove duplicates in the Data tab and have only column B and C ticked.
That gives:

Thank you for an example of the Regex. You example is very clear and concise. I ultimately ended up using the "Like operator" for my project. However, I did find that basically my choice to use brackets " [ ] " in my strings to search through was a bad idea because the right bracket " ] " is basically an illegal character for the "Like operator." So... I realized this is likely why everyone is suggesting Regex. However, In this particular situation I have the option to change the strings I will be receiving so I simply changed from [EX==] to — XCELLGUY, Aug 22 '19 at 16:42
I am using loops and Like "tag:(" & Num & "###)" & Suffix where Num is an array of single digit numbers and Suffix is and array of things like "", "", etc... — XCELLGUY, Aug 22 '19 at 16:44
So you want to include the `[]` in column C? Then use pattern: `"tag:\((\d)\d{3}\)\([\w{2}.{2}\])"` — Andreas, Aug 22 '19 at 18:33

Loop through and match string with wildcards

1 Answers1