Tag Archives: wildcard

VBA Like Operator – Using Wildcards in Conditional Statements

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 5.00 out of 5)
Loading...

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:

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.

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

Matching against numbers

Matching a phone number with either dashes or dots

Matching a certain string within another string

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: