vba like operator

VBA Like Operator – Using Wildcards in Conditional Statements

Written by on in Excel, MS Office, Outlook, PowerPoint, Word

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 Strings Manipulations Resources

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:

Wildcard Description
* 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
The Like operator is not letter case sensitive! In case you need upper/lower case matching use VBA InStr function instead

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.

You can find the official Like MSDN documentation here

Conculsions on the VBA Like operator

My main take-aways are:

Related posts: