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!
VBA Like operator
The VBA Like operator is a boolean operator that return True if a string is matched against a certain string pattern.
"Dog and Cat" Like "*Dog*" 'Result: True "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 VBA 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
"123-345-678" Like "###[-.]###[-.]###"
Matching a certain string within another string
"fewfwfewfwefdogfefweff" Like "*dog*"
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: