VBA: A Simple Formula for “If Cell Contains”

You can use the following basic syntax to use a formula for “if cell contains” in VBA:

Sub IfContains()
    Dim i As Integer

    For i = 2 To 8
        If InStr(1, LCase(Range("A" & i)), "turtle") <> 0 Then
        Result = "Contains Turtle"
        Else
        Result = "Does Not Contain Turtle"
        End If
    Range("B" & i) = Result
    Next i
End Sub

This particular example checks if each cell in the range A2:A8 contains “turtle” and then assigns either “Contains Turtle” or “Does Not Contain Turtle” to each corresponding cell in the range B2:B8.

Note: The Instr method checks if one string contains another string and the LCase method converts text to lowercase to perform a case-insensitive search.

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

Example: How to Use “If Cell Contains” in VBA

Suppose we have the following list of cells in Excel that each contain various text:

Suppose we would like to check if each cell in the range A2:A8 contains the text “turtle” and output the results in the corresponding cells in the range B2:B8.

We can create the following macro to do so:

Sub IfContains()
    Dim i As Integer

    For i = 2 To 8
        If InStr(1, LCase(Range("A" & i)), "turtle") <> 0 Then
        Result = "Contains Turtle"
        Else
        Result = "Does Not Contain Turtle"
        End If
    Range("B" & i) = Result
    Next i
End Sub

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

Column B tells us whether or not the corresponding cells in column A contain “turtle” somewhere in the text.

Note: You can find the complete documentation for the VBA Instr method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String