How to use Like Operator in VBA (With Examples)

---

You can use the Like operator in VBA to determine if a string contains a specific pattern.

For example, you can use the following syntax to check if each string in the cell range A2:A10 contains the substring “hot” and output the results in the range B2:B10:

Sub CheckLike()

Dim i As Integer
    
For i = 2 To 10
    If Range("A" & i) Like "*hot*" Then
        Range("B" & i) = "Contains hot"
    Else
        Range("B" & i) = "Does not contain hot"
    End If
Next i
    
End Sub

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

Example: How to Use Like Operator in VBA

Suppose we have the following list of foods in Excel:

We can create the following macro to check if each string in column A contains the substring “hot” and output the results in column B:

Sub CheckLike()

Dim i As Integer
    
For i = 2 To 10
    If Range("A" & i) Like "*hot*" Then
        Range("B" & i) = "Contains hot"
    Else
        Range("B" & i) = "Does not contain hot"
    End If
Next i
    
End Sub

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

Column B shows whether or not each corresponding cell in column A contains the substring “hot” or not.

Note that we used asterisks ( * ) around the substring to indicate that any character can come before or after the string “hot” in the cell.

If we would instead like to check if each string starts with “hot” then we could place an asterisk only after the substring:

Sub CheckLike()

Dim i As Integer
    
For i = 2 To 10
    If Range("A" & i) Like "hot*" Then
        Range("B" & i) = "Starts with hot"
    Else
        Range("B" & i) = "Does not start with hot"
    End If
Next i
    
End Sub

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

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