VBA Like Operator

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on July 19, 2021

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:

Sub LikeDemo()

Dim strName As String
Dim blnResult As Boolean

strName = "Mr. Michael James" 

If strName Like "Mr*" Then
    blnResult = True
Else
    blnResult = False
End If

End Sub

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:

strName = "Mr. Michael James"

Then we use the Like operator in the If statement:

If strName Like "Mr*" Then
    blnResult = True
Else
    blnResult = False
End If

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:

strText1 = "ABCDE"
  
If strText1 Like "AB?DE" Then
    blnResult1 = True
Else
    blnResult1 = False
End If

 

Matching a single digit:

strText2 = "AB7DE"
  
If strText2 Like "AB#DE" Then
    blnResult2 = True
Else
    blnResult2 = False
End If

 

Matching any uppercase character from the alphabet:

strText3 = "ABCDE"

If strText3 Like "AB[A-Z]DE" Then
    blnResult3 = True
Else
    blnResult3 = False
End If

 

Not matching any uppercase character from the alphabet:

strText4 = "AB7DE"

If strText4 Like "AB[!A-Z]DE" Then
    blnResult4 = True
Else
    blnResult4 = False
End If

 

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

strText5 = "ABcDE"
   
If strText5 Like "AB[A-Za-z]DE" Then
    blnResult5 = True
Else
    blnResult5 = False
End If

 

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples