VBA: How to Check if String Contains Another String

You can use the Instr() function in VBA to check if a string contains another string.

This function uses the following basic syntax:

Instr(start, string1, string2, …)

where:

start: The starting position for the search
string1: The string to search
string2: The string you’re attempting to find

The following example shows how to use this function in practice.

Example: Use VBA to Check if String Contains Another String

Suppose we have the following dataset in Excel that contains information about various basketball teams:

Now suppose that we would like to check if each string in the Team column contains “avs” somewhere in the string.

We can use the following syntax in VBA to check if each string in the Team column contains “avs” and, if so, return the entire row in the columns D and E:

Sub StringContains()
Dim i As Integer, i_num As Integer

For i = 2 To 9
    If InStr(1, LCase(Range("A" & i)), "avs") <> 0 Then
        i_num = i_num + 1
        Range("D" & i_num & ":E" & i_num) = Range("A" & i & ":B" & i).Value
    End If
Next i
End Sub

When we run this macro, we receive the following output:

Notice that each of the rows that contained the string “avs” somewhere in the Team column are returned in columns D and E.

In this example, only two teams (Mavs and Cavs) contained “avs” in the Team column.

To search for a different string, simply replace “avs” in the code above with the string of your choice.

Note #1: In the code we used For i = 2 to 9 since cells A2 to A9 contained the strings we were interested in. Feel free to change this range depending on the cell range you’re searching.

Note #2: You can find the complete documentation for the Instr() function in VBA here.