VBA Like Operator
Written by
Reviewed by
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
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](https://www.automateexcel.com/excel/images/AutoMacro/vba-workbook-save-as.png)
Learn More!