Searching for String inside another (with interruptions), on Excel

Question

I'm trying to check whether the main string contains the entire substring, even if there are interruptions.

For example:

main string = 12ab34cd,
substring = 1234d

should return a positive, since 1234d is entirely contained in my main string, even though there are extra characters.

Since InStr doesn't take wildcards, I wrote my own VBA using the mid function, which works well if there are extra characters at the start/end, but not with extra characters in the middle.

In the above example, the function I wrote

works if the main string is ab1234dc,
but not if it's 12ab34cd.

Is there a way to accomplish what I'm trying to do using VBA?

Your best bet is probably Regex. See https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Fred, Dec 11 '17 at 21:11
[Regular Expressions]( https://learn.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference) are definitely the way to go. — ashleedawg, Dec 11 '17 at 23:02

Ron Rosenfeld · Answer 1 · 2017-12-12T03:00:48.077

Note Both of the methods below are case sensitive. To make them case insensitive, you can either use Ucase (or Lcase) to create phrases with the same case, or you can prefix the routine with the Option Compare Text statement.

Although this can be done with regular expressions, here's a method using Mid and Instr

Option Explicit
Function ssFind(findStr, mainStr) As Boolean
    Dim I As Long, J As Long

I = 1: J = 1
Do Until I > Len(findStr)
    J = InStr(J, mainStr, Mid(findStr, I, 1))
    If J = 0 Then
        ssFind = False
        Exit Function
    End If
    I = I + 1: J = J + 1
Loop
ssFind = True

End Function

Actually, you can shorten the code further using Like:

Option Explicit
Function ssFind(findStr, mainStr) As Boolean
    Dim I As Long
    Dim S As String

For I = 1 To Len(findStr)
    S = S & "*" & Mid(findStr, I, 1)
Next I
S = S & "*"

ssFind = mainStr Like S
End Function

AmBo · Answer 2 · 2017-12-12T02:02:00.063

Assuming you have 3 columns "SUBSTR","MAIN" and "CHECK" and your "Substring" data range is named "SUBSTR"

Sub check_char()

Dim c As Range

For Each c In Range("SUBSTR")
    a = 1
    test = ""
    For i = 1 To Len(c.Offset(0, 1))
        If Mid(c.Offset(0, 1), i, 1) = Mid(c, a, 1) Then
            test = test & Mid(c.Offset(0, 1), i, 1)
            a = a + 1
        End If
    Next i
    If test = c Then
        c.Offset(0, 2) = "MATCH"
    Else
        c.Offset(0, 2) = "NO MATCH"
    End If
Next

End Sub

Searching for String inside another (with interruptions), on Excel

2 Answers2