Tag Archives: wildcard

vba like operator

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 VBA Like operator:

The VBA 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

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

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.

Conculsions on the VBA Like operator

My main take-aways are: