VBA Like Operator

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:

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:

Image 2. Using the Like operator with different matching patterns