Determining the functional length of a pattern in a "Like" comparison

Is there a way to determine the functional length of a pattern in a "Like" comparison?

For example, "[A-Z]" has a functional length of 1, but Len("[A-Z]") gives the answer 5 (because the string itself is five characters long).

I think being able to determine the functional length of the pattern would enable a more flexible way of looking through the string.

I have working code but it relies on the string being searched having spaces, and the substring to be found having a space on either side.

This is the code I tried; it doesn't work, because len(pattern) is longer than the pattern I'm trying to find - in my specific example the pattern is #####[A-Z][A-Z], to find (for example) 11111AA or 22222BB - five numbers followed by two upper case letters.

Function ExtractMatch(Source As String, Pattern As String) As String
' extracts a substring matching the given pattern

    Dim i As Long, lenP As Long, Test As String
    lenP = Len(Pattern)
        For i = 1 To Len(Source) - lenP
            Test = Mid(Source, i, lenP)
            Debug.Print Test
            If Test Like Pattern Then
            ExtractMatch = Test
        Exit For
    End If
    Next
    
End Function

For context I am trying to extract a quote number from somewhere in a hand-typed entry. Sample data and desired output - line 5 is currently not captured as there's a typo, and no space before the quote number.

Sample input/output

Assuming that there are no * in the pattern we can get the length of the pattern using this function:

Function getPatterLength(ByVal Pattern As String) As Long
    If InStr(Pattern, "*") > 0 Then
        getPatterLength = -1
        Exit Function
    End If

    Dim Parts() As String
    Parts = Split(Pattern, "[")

    Dim i As Long
    For i = 0 To UBound(Parts)
        If InStr(Parts(i), "]") > 0 Then
            Parts(i) = Mid(Parts(i), InStr(Parts(i), "]"))
        End If
    Next

    getPatterLength = Len(Join(Parts, ""))
End Function

It would be easier to add a parameter to the extract function for the pattern length.

Function ExtractMatch(Source As String, Pattern As String, PatterLength as long) As String
' extracts a substring matching the given pattern

    Dim i As Long, Test As String
        For i = 1 To Len(Source) - PatterLength
            Test = Mid(Source, i, PatterLength)
            Debug.Print Test
            If Test Like Pattern Then
            ExtractMatch = Test
            Exit Function
        End If

        Exit For
    End If
    Next
    
End Function

Thought it would be fun (for some at least) to see if this can be done through FILTERXML():

Formula in B2:

=MAP(A2:A5,LAMBDA(x,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,MID(x,SEQUENCE(LEN(x)),7))&"</s></t>","//s[string-length(normalize-space())=7][substring(.,1,5)*0=0][translate(substring(.,6),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''][1]")))

The deconstructed xpath anding predicates:

• [string-length(normalize-space())=7] - We need to test if the node's length equals 7 characters (after normalization: This is important due to the working of the 3rd predicate). In regex terms: ^.{7}$;

• [substring(.,1,5)*0=0] - Test that the first 5 characters of the node are numeric. In regex terms: ^\d{5};

• [translate(substring(.,6),'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')=''] - Assert that the last two characters are uppercase alpha chars. In regex terms: [A-Z]{2}$;

• [1] - Retrieve the 1st node from resulting filtered node-list.

You can use Regular Expressions to extract a submatch like below:

Public Sub Example()
    Debug.Print ExtractSubstring("Quote number 11111AA refers", "[0-9]{5}[A-Z]{2}")
End Sub

Public Function ExtractSubstring(ByVal InputString As String, ByVal Pattern As String) As String
    Dim RetVal As String
    Dim AllMatches As Object
    Dim RegEx As Object
    Set RegEx = CreateObject("vbscript.regexp")
    
    With RegEx
        .Pattern = Pattern
        .Global = True
        .IgnoreCase = True  ' change this if you want case sensitivity
    End With
    Set AllMatches = RegEx.Execute(InputString)
    
    If AllMatches.Count <> 0 Then
        RetVal = AllMatches.Item(0)
    End If
    
    ExtractSubstring = RetVal
End Function

Or even use it directly like a UDF (User Defined Function)

=ExtractSubstring(A2,"[0-9]{5}[A-Z]{2}")

Note this will only return the first match if there are more matches with this pattern.

Why don't you use regular expressions:

Public Sub test()

Dim pattern As String
pattern = "\d{5}[A-Z]{2}"

Debug.Print regex_PatternMatch(pattern, "11111AA")  'will return true
Debug.Print regex_PatternMatch(pattern, "111AA")   'will return false

End Sub

Public Function regex_PatternMatch(pattern As String, textToCheck As String) As Boolean
    Dim regEx As New RegExp
    With regEx
        .pattern = pattern
        regex_PatternMatch = .test(textToCheck)
    End With
End Function

You have to add a reference to VB Script regular expressions