The VBA Like operator is something so useful I am often surprised how rarely it is used in Excel and Access VBA. I often tend to see the Like operator as the last resort before using Regular Expressions in VBA. It replaces greatly the VBA InStr function when needing to check if a certain substring is present within a certain string. So let’s get right to it!
Here is a simple example of using the Like operator:
Dim val As String: val = "Dog and Cat" If val Like "*Dog*" Then Debug.Print "Found Dog" End If 'Result: "Found Dog"
The Like operator is an easier alternative to VBA Regex which allows you to search for almost any pattern within a string and find matches.
In case you want to not only find check if the string contains a substring but also return the position of the match – best try using the VBA InStr function.
VBA Like operator
The VBA Like operator is a boolean operator that return True if a string is matched against a certain string pattern.
Debug.Print "Dog and Cat" Like "*Dog*" 'Result: True Debug.Print "Dog and Cat" Like "*Cow*" 'Result: False
VBA Like allows you also to use the following wildcards to replace certain strings or characters:
|*||matches any number of characters|
|?||matches any 1 character|
|[ ]||matches any 1 character specified between the brackets|
|–||matches any range of characters e.g. [a-z] matches any non-capital 1 letter of the alphabet||#||matches any digit character|
And that is basically it. Easy right? Let’s see some examples…
VBA Like operator examples
Let us now look at a couple of examples that greatly show all the possibilities of the Like operator:
Matching against letters
If "Animal" Like "[A-Z]*" then Debug.Print "Match: String starting with Capital letter!" End If
Matching against numbers
If "My house number is 22" Like "*##" then Debug.Print "Match: String contains a 2 digit number" End If
Matching a phone number with either dashes or dots
Debug.Print "123-345-678" Like "###[-.]###[-.]###" 'Result: True
Matching a certain string within another string
Debug.Print "fewfwfewfwefdogfefweff" Like "*dog*" 'Result: True
As you can see in the first row we are using the Like Operator similarly as we use other compare operators (=, >, <, <>). The Like operator let’s you validate if a string on the left side of the operator matches the Like expression on the right.
Conculsions on the VBA Like operator
My main take-aways are: