VBA Like Operator

Associated Files Download Links

In VBA, you can compare two strings using the Like operator to check matching of the strings. In this tutorial, you will learn how to use this operator with different patterns.

If you want to learn how to compare strings in VBA, click here: VBA Compare Strings – StrComp

If you want to learn how to use comparison operators, click here: VBA Comparison Operators – Not Equal to & More

 

Using the Like Operator to Compare Two Strings

With Like operator, we can check if a string begins with a specific text, includes it, etc. By default, the Like operator compares characters using the Binary method. This means that the operator is case-sensitive. If you want to make it case-insensitive, you need to put Option Compare Text at the top of your module. Using this method, the Like operator considers “S” and “s” the same characters. In our examples, we will use the default, case-sensitive comparison.

If the matching exists, the Like operator returns True as a result, or False otherwise.

First, we will look at the simple example where we want to check if our string variable begins with Mr. To do this, you need to put an asterisk (*) at the end of the matching text (Mr*). Here is the code:

In this example, we want to check if string strName begins with Mr and return True or False in the variable blnResult.

First, we set the value of strName to Mr. Michael James:

Then we use the Like operator in the If statement:

As the strName begins with Mr, the blnResult returns True:

vba like operator begins with

Image 1. Using the Like operator to check if the string begins with certain characters

 

Using the Like Operator with Different Matching Patterns

The Like operator can check matching of two strings based on different patterns. Here is the list of possible matching patterns:

Pattern code

Type of matching

*

Matches 0 or more characters

?

Matches a single character

#

Matches a single digit

[chars]

Matches a single character from a char list

[A-Z]

Matches any uppercase character from the alphabet

[A-Za-z]

Matches any character from the alphabet

[!chars]

Matches a single character excluding a char list

 

Now we can see how to use these patterns in the code. Here is the example for multiple patterns:

Matching a single character:

 

Matching a single digit:

 

Matching any uppercase character from the alphabet:

 

Not matching any uppercase character from the alphabet:

 

Matching any character from the alphabet (uppercase or lowercase):

 

When you execute the code, you can see that the Like operator returns True in blnResult variables for every comparison:

vba like operator different matching patterns

Image 2. Using the Like operator with different matching patterns

[yuzo]