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, …)
- 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.
The following tutorials explain how to perform other common tasks using VBA: