JIYIK CN >

This article demonstrates the use of Instr()the , , InstrRev()and Likefunctions to check whether a main string contains a substring.

Use Instr()the function to check if the main string contains a substring

Instr()Function syntax:

InStr([ start ], string1, string2, [ compare ])

Return type: Integer

parameter:

The following code block will use Instr()the function to check if a substring is within the main string in VBA.

Function IsSubstring(pos as Integer, mainStr as String, subStr as String,compTyp as Integer) as boolean
    'if `Instr()` function returned 0 then the substring is not present in the main string.
    'If `Instr()` function returned a value greater than `0`, would mean that the substring is in the main string.
    If Instr(pos,mainStr,subStr,compTyp) >0 Then
        IsSubstring = true
        Else: IsSubstring = false
    End if
End Function
Sub test1()
    Debug.print IsSubstring(1,"ABCDE","C",1)
End Sub
Sub test2()
    Debug.print IsSubstring(1,"ABCDE","F",1)
End Sub
Sub test3()
    Debug.print IsSubstring(1,"ABCDE","c",0)
End Sub

Output test1:

True

Output test2:

False

Output test3:

False

The following code block will use Instr()the function to return the position of a substring from the main string.

Function GetPosition(pos as Integer, mainStr as String, subStr as String,compTyp as Integer)
    'Check first if the substring is in the main string.
     If InStr(pos, mainStr, subStr, compTyp) > 0 Then
        'if substring is in the main string then get the position of the substring in the main string.
       GetPosition = InStr(1, mainStr, subStr, 1)
       Else: GetPosition = ("Subtring is not in the main string.")
   End If
End Function
Sub test1()
    'Check if `C` is in `ABCDE` starting at the first letter (A), case insensitive.
    Debug.Print GetPosition(1,"ABCDE", "C",1)
End Sub
Sub test2()
    'Check if `c` is in `ABCDE` starting at the first letter of the main string, case sensitive.
    Debug.Print GetPosition(1,"ABCDE", "c",0)
End Sub
Sub test3()
    'Check if `c` is in `ABCDE` starting at the fourth letter of the main string, case sensitive.
    Debug.Print GetPosition(4,"ABCDE", "c",0)
End Sub

Output test1:

3

Output test2:

Subtring is not in the main string.

Output test3:

Subtring is not in the main string.

Use InstrRev()the function to check if the main string contains a substring

InstrRev()Function syntax:

InStrRev(string1, string2,[ start ], [ compare ])

Return type: Integer

parameter:

The following code block will use InstrRev()the function to check if a substring is within the main string in VBA.

Function IsSubstring(mainStr As String, subStr As String) As Boolean
    'if `InstrRev()` function returned 0 then the substring is not present in the main string.
    'If `InstrRev()` function returned a value greater than `0`, would mean that the substring is in the main string.
    If InStrRev(mainStr, subStr) > 0 Then
        IsSubstring = True
        Else: IsSubstring = False
    End If
End Function
Sub test1()
    Debug.Print IsSubstring("ABCDE", "C")
End Sub
Sub test2()
    Debug.Print IsSubstring("ABCDE", "F")
End Sub

Output test1:

True

Output test2:

False

Use Likethe operator to check if the main string contains a substring

LikeOperator syntax:

res = string Like pattern

Return type: Boolean

parameter:

Remark:

The following code block will use Likethe operator to check if a substring is within the main string in VBA

Function IsSubString(mainStr as String,subStr as String) as Boolean
    'Check if subStr is in the main string by using *
   If mainStr Like "*" & subStr & "*" Then
        IsSubString = True
        Else: IsSubstring= False
   End If
End Function
Sub test1()
    Debug.print (IsSubString("ABCDE","C"))
End Sub
Sub test2()
    Debug.print (IsSubString("ABCDE","c"))
End Sub
Sub test3()
    Debug.print (IsSubString("ABCDE","F"))
End Sub

Output test1:

True

Output test2:

False

Output test3:

False