Table of Contents
VBA is a programming language used in Microsoft Office applications like Excel and Word. This VBA code snippet checks if a string (e.g. a sentence) contains another string (e.g. a word). It is a useful tool to have when you need to find out if a certain string is contained within another string. It is also useful for finding out if a certain word is present in a larger body of text. It can be used to automate tasks and to make more efficient use of time.
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 #2: You can find the complete documentation for the Instr() function in VBA .
The following tutorials explain how to perform other common tasks using VBA: