How to Use Wildcards in VBA (With Examples)

You can use the Like operator in VBA along with the following built-in wildcard characters to search for specific patterns in strings:

* : Matches any number of characters
? : Matches a single character
# : Matches a single digit
[] : Matches any character in range

The following examples show how to use these wildcard characters in practice.

**Example 1: Use * Wildcard To Search for Substring**

Suppose we have the following list of foods in column A:

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

Sub FindString()

    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:

**Example 2: Use * Wildcard To Search for Strings that End in Pattern**

Suppose we have the following list of basketball team names in column A:

We can create the following macro to search for strings in column A that end in “ets” and output the results in column B:

Sub FindEndingString()

    Dim i As Integer
    
    For i = 2 To 10
        If Range("A" & i) Like "*ets" Then
            Range("B" & i) = "Ends in ets"
        Else
            Range("B" & i) = "Does Not End in ets"
        End If
    Next i
    
End Sub

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

Example 3: Use # Wildcard To Search for Numbers

Suppose we have the following list of strings in column A:

We can create the following macro to search for strings in column A that contain numbers and output the results in column B:

Sub FindNumbers()

    Dim i As Integer
    
    For i = 2 To 10
        If Range("A" & i) Like "*#*" Then
            Range("B" & i) = "Contains Numbers"
        Else
            Range("B" & i) = "Does Not Contain Numbers"
        End If
    Next i
    
End Sub

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

Example 4: Use [] Wildcard To Search for Several Characters

Suppose we have the following list of basketball team names in column A:

We can create the following macro to search for strings in column A that contain an r, s or t somewhere in the team name and output the results in column B:

Sub FindSpecificLetters()

    Dim i As Integer
    
    For i = 2 To 10
        If Range("A" & i) Like "*[r-t]*" Then
            Range("B" & i) = "Contains r, s, or t"
        Else
            Range("B" & i) = "Does Not Contain r, s or t"
        End If
    Next i
    
End Sub