vba like operator

VBA Like operator – using wildcards in conditional statements

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

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:

  1. * – matches any number of characters
  2. ? – matches any 1 character
  3. [ ] – matches any 1 character specified between the brackets
  4. - – matches any range of characters e.g. [a-z] matches any non-capital 1 letter of the alphabet
  5. # – 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:

Related Posts

Leave a Reply