How to Check If String Contains an Another String - VBA

When we develop Excel VBA applications sometimes we need the application to check whether some strings are included in other strings. So in this lesson you will learn how to check if a string contains a substring using the VBA InStr function.

Table of contents
InStr function
Return values of VBA InStr function
VBA InStr case insensitive search
VBA InStr case sensitive search
If the second string occurs multiple times.
How to use VBA InStr Function for list of strings

InStr function

The InStr function has four parameters. Two optional and two required parameters. This is the syntax of the InStr function.

InStr([start], FirstString, SecondString, [CompareMethod])

Start - This is the starting point of the first string where you want to begin searching for the second string. If omitted, the function will search from first position.

FirstString - Function will search through this string to find the second string.

SecondString - This is the string the InStr function will search for.

CompareMethod - There are three options for this parameter. vbBinaryCompare, vbDatabaseCompare and vbTextCompare. But vbDatabaseCompare is only used for Microsoft Access. So you can use either vbTextCompare or vbBinaryCompare for Excel VBA Macros. If you select vbBinaryCompare then the VBA InStr function will carry out a binary comparison. So the function will see “A” and “a” as different. But if you choose vbTextCompare then the InStr function will carry out a textual comparison and it will see “A” and “a” as the same. You will get a clear understanding about these different types of comparisons from the examples below.

Return values of VBA InStr function

There are four types of return values for this function. Click on the links to see related examples.

0 - Second string is not found or Length of first string is 0 or Starting point is higher than length of first string or Starting point is higher than the occurrence position of the second string.

Null - First string is null or Second string is Null or both are Null

Start - Length of second string is 0

Position at first string where match is found - When second string is found within first string

Now let’s look at examples where we will get those return types.

Return 0

Second sting is not found

Sub Example_1()

     Debug.Print InStr(1, "Excel VBA Solutions", "PHP", vbTextCompare)

End Sub

Length of first string is 0

In this example Len(String1) equals 0. So the function returns 0.

Sub Example_2()

     Dim String1 As String
     Dim String2 As String

     String1 = ""
     String2 = "PHP"

     Debug.Print InStr(1, String1, "PHP", vbTextCompare)

End Sub

Starting point is higher than length of first string

Sub Example_3()

     Debug.Print InStr(20, "run macro", "macro", vbTextCompare)

End Sub

Here the length of the first string is 9. But the start is set to 20. So the function will return 0.

Start is higher than occurrence position of the second string inside first string

Sub Example_4()

     Debug.Print InStr(8, "Check this Excel tutorial", "this", vbTextCompare)

End Sub

In this example, the InStr function is searching for the string “this” inside the first string. And string “this” appears at the position 7 of the first string. But as the start is set to 8 the function returns 0. Because the InStr function can’t find the substring “this” after position 8.

Return Null

The InStr function returns Null on three occasions.

Return Null when first string is Null

Sub Example_5()

     Dim String1 As Variant
     Dim String2 As String

     String1 = Null
     String2 = "word"

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Here String1 has been declared as a variant because only the variant data type can hold Null values.

Return Null when second string is Null

Sub Example_6()

     Dim String1 As String
     Dim String2 As Variant

     String1 = "Excel VBA Solutions"
     String2 = Null

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Here String2 is declared as a variant because only the variant data type can hold the Null values.

Return Null when both first and second strings are Null

Sub Example_7()

     Dim String1 As Variant
     Dim String2 As Variant

     String1 = Null
     String2 = Null

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

Return Start

The InStr function will return the start value on one occasion.

Return Start when length of second string is 0

Sub Example_8()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = ""

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the Len(String2) is equal to 0. And the start is 4. So the function will return 4.

Return the position where the match is found

When the second string is found within the first string, the function will return the position of the first string where the second string is found.

Sub Example_9()

     Dim String1 As String
     Dim String2 As String

     String1 = "How to check if string contains another string"
     String2 = "to"

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the word “to” can be found at the fifth position of the String1. So the function will return 5. Note that the function also considers spaces when determining the position.

VBA InStr Case Sensitivity

When you use the InStr function sometimes you may want to do case insensitive searches and sometimes case sensitive searches. So how do we control the case sensitivity? We can use the fourth parameter of the function to control the case sensitivity of the searches.

VBA InStr case insensitive search

In Excel VBA we can use one of the two values for the fourth parameter of the VBA InStr function. vbTextCompare or vbBinaryCompare. Because vbDatabaseCompare is only related to Microsoft Access. So far in our examples we used the vbTextCompare as the fourth parameter. If we use vbTextCompare as the fourth parameter, then the function will do case insensitive search.

Sub Example_10()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "vba"

     Debug.Print InStr(4, String1, String2, vbTextCompare)

End Sub

Here the word “VBA” in uppercase in the String1 and “vba” in lowercase in the String2. As we have used vbTextCompare as the fourth parameter, the InStr function will do case insensitive search and will return 7.

VBA InStr case sensitive search

So we learnt how to do case insensitive search using the VBA InStr function from the above example. We can use vbBinaryCompare as the fourth parameter to do case sensitive searches.

Sub Example_11()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "vba"

     Debug.Print InStr(4, String1, String2, vbBinaryCompare)

End Sub

Here the word “VBA” is in uppercase in the String1 and “vba” is in lowercase in the String2. As we have used vbBinaryCompare as the fourth parameter, search will be case sensitive and function will return 0.

Now let’s use the word “VBA” in uppercase in both strings and check how it works with the vbBinaryCompare option.

Sub Example_12()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel VBA Solutions"
     String2 = "VBA"

     Debug.Print InStr(4, String1, String2, vbBinaryCompare)

End Sub

If the second string occurs multiple times.

Sometimes the second string can occur multiple times inside the first string. If this happens the InStr function will return the position of the first occurrence of the second string starting from the start point.

Sub Example_13()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel formulas, Excel macros and Excel charts"
     String2 = "Excel"

     Debug.Print InStr(1, String1, String2, vbTextCompare)

End Sub

In this example word Excel occurs 3 times inside the String1. As the start is 1, the InStr function will return the position of first occurrence which is 1.

Here are the same example strings with a different start.

Sub Example_14()

     Dim String1 As String
     Dim String2 As String

     String1 = "Excel formulas, Excel macros and Excel charts"
     String2 = "Excel"

     Debug.Print InStr(5, String1, String2, vbTextCompare)

End Sub

In this example the start is set to 5. So now the function will search for the word “Excel” inside the String1 from the fifth position onward. Therefore in this example, the function will return the position of the second occurrence of the word “Excel”.

How to use VBA InStr Function for list of strings

However in practical situations you may not need to check if one string contains a substring. Instead you may need to check if a list of strings contains a particular substring and output the results. So now let’s look at how to accomplish such a task with the help of the For Next statement.

Let’s consider this sample Excel sheet.

This sample Excel sheet contains a list of post titles of this blog in column A. I’m going to find which titles have the word “Excel” and write “Found” Or “Not Found” in column B. Let’s name the subroutine as CheckForWordExcel

Sub CheckForWordExcel()

End Sub

First we need to declare a few variables.

Dim WS As Worksheet
Dim PostTitle As String
Dim i As Integer

If the name of the worksheet is “Sheet1”, we can assign the sheet to the WS variable as follows.

Set WS = Worksheets("Sheet1")

Assume there are titles up to the 100th row. So we can use a For Next statement like this.

For i = 2 To 100

Next i

In each iteration we can assign the post titles to the PostTitle variable like this.

For i = 2 To 100
     PostTitle = WS.Range("A" & i).Value
Next i

Now we can use the InStr function to check whether the word “Excel” is available in each title.

InStr(1, PostTitle, "Excel", vbTextCompare)

Here the second string is “Excel” and the length of it is higher than 0. Therefore InStr function should return positive value only when substring “Excel” found inside the PostTitle. So we can use an If statement inside the For Next Loop like this.

For i = 2 To 100
     PostTitle = WS.Range("A" & i).Value

     If InStr(1, PostTitle, "Excel", vbTextCompare) > 0 Then
         WS.Range("B" & i).Value = "Found"
     Else
         WS.Range("B" & i).Value = "Not Found"
     End If
Next i

So here is the full code of the subroutine.

Sub CheckForWordExcel()

     Dim WS As Worksheet
     Dim PostTitle As String
     Dim i As Integer

     Set WS = Worksheets("Sheet1")

     For i = 2 To 100
         PostTitle = WS.Range("A" & i).Value

         If InStr(1, PostTitle, "Excel", vbTextCompare) > 0 Then
             WS.Range("B" & i).Value = "Found"
         Else
             WS.Range("B" & i).Value = "Not Found"
         End If
     Next i

End Sub