How to Check if a String Contains a Substring in VBA

  2. HowTo
  3. VBA Howtos
  4. How to Check if a String Contains a …
Glen Alfaro Feb 02, 2024
VBA VBA String
  1. Using the Instr() Function to Check if the Main String Contains a Substring
  2. Using the InstrRev() Function to Check if the Main String Contains a Substring
  3. Using the Like Operator to Check if the Main String Contains a Substring
How to Check if a String Contains a Substring in VBA

This article will demonstrate the use of the Instr() function, InstrRev() Function, and Like Function to check if the main string contains a substring.

Using the Instr() Function to Check if the Main String Contains a Substring

Instr() Function Syntax:

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

Return Type: Integer

Parameters:
[ start ] Optional. Numeric value where the search will start.
For the [ start ] argument, below are the corresponding values:
1 - [Default] The search will start at the beginning of the main string
n - The search will start at n position.
string1 Mandatory. The string to be searched(main string)
string2 Mandatory. The string to find.
[ compare ] Optional. Dictates which string comparison method will be used.
For the [ compare ] argument, below are the corresponding values:
0 - [Default] Binary comparison method(case sensitive)
1 - Text comparison method (case insensitive)

Below code block will check if a substring is in the main string in VBA using Instr() Function.

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

Below code block will return the position of the substring from the main string using the Instr() Function.

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.

Using the InstrRev() Function to Check if the Main String Contains a Substring

InstrRev() Function Syntax:

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

Return Type: Integer

Parameters:
string1 Mandatory. The string to be searched(main string)
string2 Mandatory. The string to find.

Below code block will check if a substring is in the main string in VBA using InstrRev() Function.

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

Using the Like Operator to Check if the Main String Contains a Substring

Like Operator Syntax:

res = string Like pattern

Return Type: Boolean

Parameters:
res Mandatory. Return value in Boolean
string Mandatory. The string to look into
pattern Mandatory. The string to look for. See Remarks for further details

Remarks:
? Any single character
* Any 0 to many characters
# Any single number (0 till 9)

Below code block will check if a substring is in the main string in VBA using Like Operator

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

Related Article - VBA String