Comparing two strings with wildcard conditions

Question

Working on a VBA macro that compares string values, and have hit a wall at a point.

Here is some context on what I'm trying to accomplish: Compare two strings, If String 2 is CONTAINED anywhere in String 1, I return a match. For the most part, I've used the builtin "instr" function in instances where the String 2 is contained in String 1 without any wildcards involved.

The trouble I'm having is that I must treat spaces or " " in String 2 as a wildcard. Ex:

String 1 = Red Green Blue

String 2 = Red Blue

This should still return a valid match, since the " " in String 2 is being treated as a wildcard, and any number of characters can be between "Red" and "Blue"

What I did was use the "split" function with " " as a delimiter to split String 2 in instances where a space(" ") is involved, and run an instr function on the resulting array to check if each element of the array is contained in String 1. In the example above:

String 2 would be split into a String array(let's call with splitString) with the following elements: splitString = (Red, Blue)

Using the logic above:

splitString(0) is contained in String 1

splitString(1) is contained in String 1

Therefore, String 2 is contained in String 1, and a match is returned. The match condition I am using is utilizing the UBounds value of splitString (details in the code snippet below)

The issue I am having is that I need to only return a match where the initial string order of String 2 is maintained. Ex: If: String 1 = Red Green Blue String 2 = Blue Red This is not a valid match since even though when we split String 2, we find the resulting array elements are "contained" in String 1, the order of String 2 is not being respected.

Here is a rough draft of the logic I've coded:

splitString = Split(String2," ")
x = -1
For y = LBound(splitString) To UBound(splitString)
     splitStringCompare = InStr(1, String1, splitString(y), vbTextCompare)
     If splitStringCompare > 0 Then
        x = x + 1
         If x = UBound(splitString) Then
            "Match"
             Else
             "No Match"
         End If
Next y

Any help or nudge in the right direction would be much appreciated. Thanks!!

user_8759927 · Answer

You can use Regular Expressions object to test this and verify match. For demonstration purpose, I have created a UDF as below.

Public Function MatchWildCard(strMatchWith As String, strMatchString As String) As Boolean
    strMatchString = Replace(Trim(strMatchString), " ", ".+")
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = False
        .IgnoreCase = True '\ Change to False if match is case sensitive
        .Pattern = strMatchString
        If .Test(strMatchWith) Then
            MatchWildCard = True
        Else
            MatchWildCard = False
        End If
    End With
End Function

It can then be used in Excel sheet like below snapshot =MatchWildCard(A1,B1):

Note: I am a basic user of RegExp so there may be a better manner of handling this so you should test this on large sample to validate.

Comparing two strings with wildcard conditions

1 Answers