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