Table of Contents
Wildcards are a powerful tool in VBA that enable you to search for data within strings. Wildcards are used in conjunction with the Like operator in VBA and provide you with the ability to search for portions of a string within a larger string. The two wildcards used in VBA are the asterisk (*) and the question mark (?). The asterisk (*) is a wildcard for any number of characters, while the question mark (?) is a wildcard for a single character. Wildcards provide you with the flexibility to search for any number of characters or a single character within a larger string.
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 .
The following tutorials explain how to perform other common tasks using VBA: