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

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

Note: You can find the complete documentation for the VBA wildcard characters here.

Additional Resources

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

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *