Using VBA Like to Compare Strings
Contents
What is a Regular Expression? How is the Like Operator Related?
In any programming language, there is a way to compare strings against some patterns. Several characters are used to create these patterns. These patterns are called regular expressions.
Like in structured query language, VBA offers the “Like” operator which can help us compare strings against patterns or other strings. This works with wildcard characters.
List of Wildcard Characters
| S.no | Wild Card Character | Description |
| 1 | ? ( Question Mark) | This is used to match any one character from the string. For example, if we have a string “CAT,” and the pattern is “C?T,” then VBA LIKE operator returns TRUE. If the string is “CATCH and the patterns are “C?T,” then VBA LIKE operator returns FALSE. |
| 2 | * (Asterisk) | This matches zero or more characters. For example, if the string is “Good,” and the pattern is “G**d,” VBA LIKE operator returns TRUE. |
| 3 | [] (square brackets) | This matches any one single character specified in the brackets. |
| 4 | [<single character within a range>] | This matches any single character in the range Char-Char. |
| 5 | [!<list of single characters>] | This matches any single character not in the list. |
| 6 | [!<single character within a range>] | This matches any single character not in the range Char-Char. |
| 7 | # ( hash) | This matches any single digit. |
Examples of Programs That Use the Like Operator
Example Program: The “*” Wildcard Character
Scenario: A shop offers to give away a pen as a gift to customers who buy any ice cream. We are developing a code that will consider the customers who purchase any item with the words ice and cream in them. For this we will use the asterisk wildcard (*) character.
Sub likeoperator_Demo()
' declare a variable
Dim var_item
' receive input from the customer in the form of item number
var_item = InputBox("Enter the item you wish to buy")
' validate if the product is eligible for free gift. The "*" stands for any number of "any character(s)".
If var_item Like "*ice*cream*" Then
' eligible
Debug.Print "You item '" & var_item & "' is eligible for the free gift - pen"
Else
' not eligible
Debug.Print "You item '" & var_item & "' is not eligible for the free gift - pen"
End If
End Sub
Output for Various Products:
In this output you might have noticed that only the products with “ice” and “cream” in lower case, with or without any characters in between in the same order (“cream” after “ice”), are eligible for the offer. Any products with “ice”/“cream” alone in their product names are not eligible for the offer.
This shows that the comparison we have done in the “if” conditional clause is “CASE SENSITIVE.”
To make my module case-insensitive i.e. to consider both capital and small letters, the statement “OPTION COMPARE TEXT” has to be used on top of the module.
Option Compare Text
Example Program: The “?” Wildcard
Here is a simple program with a “?” (question mark) in a word. This is treated as a pattern and compared with input text. Let us run this code without the “Option compare text” to see original results.
Sub wildcard_like_demo()
' declare a string variable and an input variable
Dim pattern1, var_input
' initialize te variable with one "?" character which can accept any character in it place to say that it matches this variable
pattern1 = "SCHOOL?BOY"
' literally we have made pattern1 a pattern.
' receive an input from the user and store it in var_input variable
var_input = InputBox("Enter a text. ")
'compare to see if there is any single character in the place of "?"
If var_input Like pattern1 Then
' if so, the input matches the pattern
Debug.Print "The input text '" & var_input & "' matches the pattern '" & pattern1 & "'"
Else
' if not, the input does not match the pattern
Debug.Print "The input text '" & var_input & "‘ does not match the pattern '" & pattern1 & "'"
End If
End Sub
Here is the output for various input text values. Please try copying and pasting these input values to understand the working of this “?” wildcard character.
The input text ‘school’ does not match the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOLBOY’ does not match the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOLBBOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOLcBOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOL&BOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOL/BOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOLzBOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOL*BOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘SCHOOL:BOY’ matches the pattern ‘SCHOOL?BOY’
The input text ‘BOY’ does not match the pattern ‘SCHOOL?BOY’
The input text ‘SCOOLBOY’ does not match the pattern ‘SCHOOL?BOY’
Example Program: The “#” Wildcard Character
In this program we are creating a pattern with a “#”. Now we try to match it with various input expressions. If the input expressions have a digit in the place of “#” and the rest of the word as it is, then the expression is said to match the created pattern. If any of the conditions mentioned above are not met, then we conclude that the input expression does not match the pattern.
Sub wildcard_like_demo()
' declare a string variable and an input variable
Dim pattern1, var_input
' initialize te variable with one "#" character which can accept any single digit in its place to say that it matches this variable / pattern
pattern1 = "GOOD#GIRL"
' literally we have made pattern1 a pattern.
' receive an input from the user and store it in var_input variable
var_input = InputBox("Enter a text. ")
'compare to see if there is any single digit in the place of "#"
If var_input Like pattern1 Then
' if so, the input matches the pattern
Debug.Print "The input text '" & var_input & "' matches the pattern '" & pattern1 & "'"
Else
' if not, the input does not match the pattern
Debug.Print "The input text '" & var_input & "‘ does not match the pattern '" & pattern1 & "'"
End If
End Sub
Output for Various Input Expressions:
The input text ‘GooGIRL’ does not match the pattern ‘GOOD#GIRL’
The input text ‘GOOD5GIRL’ matches the pattern ‘GOOD#GIRL’
The input text ‘GOODGIRL’ does not match the pattern ‘GOOD#GIRL’
The input text ‘GOOD$GIRL’ does not match the pattern ‘GOOD#GIRL’
The input text ‘GOODGGIRL’ does not match the pattern ‘GOOD#GIRL’
The input text ‘GOOD~GIRL’ does not match the pattern ‘GOOD#GIRL’
The input text ‘Good7GIRL’ does not match the pattern ‘GOOD#GIRL’
The input text ‘GOOD8GIRL’ matches the pattern ‘GOOD#GIRL’
Example Program: Single Character [] Within Square Brackets
A single character (as it is) or from a range of characters can be used as a wildcard character within square brackets. In this program we use the range A-Z as the range in the pattern and ensure that any single capital or uppercase letter is used in its place in the input expression.
The expression will match the pattern if:
- Only one since uppercase character is used in the place of [A-Z]
- All other characters are as in the pattern, in the same order.
Sub wildcard_like_demo()
' declare a string variable and an input variable
Dim pattern1, var_input
' initialize the variable with [A-Z] in it.
pattern1 = "This is [A-Z] country"
' literally we have made pattern1 a pattern.
' receive an input from the user and store it in var_input variable
var_input = InputBox("Enter a text. ")
'compare to see if there is any single character in the place of "[A-Z]"
If var_input Like pattern1 Then
' if so, the input matches the pattern
Debug.Print "The input text '" &var_input & "' matches the pattern '" & pattern1 & "'"
Else
' if not, the input does not match the pattern
Debug.Print "The input text '" & var_input & "‘ does not match the pattern '" & pattern1 & "'"
End If
End Sub
The Output for Various Input Expressions:
The input text ‘INDIA’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This is I country’ matches the pattern ‘This is [A-Z] country’
The input text ‘This is MY country’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This is a country’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This is o country’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This is A country’ matches the pattern ‘This is [A-Z] country’
The input text ‘This is 3 country’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This is % country’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This is A countr’ does not match the pattern ‘This is [A-Z] country’
The input text ‘This isPcountry’ does not match the pattern ‘This is [A-Z] country’
Example Program: Single Character Within Brackets (Lowercase) [a-z]
This example is similar to the above one with the difference that lowercase characters are used as a range and the range is not from a-z this time. It is just a subset of it i.e. [b-j]. So, any lowercase letters outside this range will not be considered to match the pattern.
Sub wildcard_like_demo()
' declare a string variable and an input variable
Dim pattern1, var_input
' initialize the variable with [A-Z] in it.
pattern1 = "This is [A-Z] country"
' literally we have made pattern1 a pattern.
' receive an input from the user and store it in var_input variable
var_input = InputBox("Enter a text. ")
'compare to see if there is any single character in the place of "[A-Z]"
If var_input Like pattern1 Then
' if so, the input matches the pattern
Debug.Print "The input text '" & var_input & "' matches the pattern '" & pattern1 & "'"
Else
' if not, the input does not match the pattern
Debug.Print "The input text '" & var_input & "‘ does not match the pattern '" & pattern1 & "'"
End If
End Sub
Output with Various Input Expressions:
The input text ‘I am [b-j] artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am c artist’ matches the pattern ‘I am [b-j] artist’
The input text ‘I am X artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am y artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am ] artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am % artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am j artist’ matches the pattern ‘I am [b-j] artist’
The input text ‘I am 4] artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am 6 artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am G artist’ does not match the pattern ‘I am [b-j] artist’
The input text ‘I am g artist’ matches the pattern ‘I am [b-j] artist’
![Example of lowercase single character within brackets that's a subset of [a-z].](https://software-solutions-online.com/wp-content/uploads/2021/07/unnamed-13-1024x334.png)
Example Program: Not in Range [!<>]
This program compares and matches each of the input expressions with a pattern that is a wildcard which denotes “not in range.” It is different from the two programs explained above.
An exclamation mark in front of a marked range, but within the same square brackets is said to denote “not in the specified range.”
For example:
[!g-j] should match any single lowercase letter from a to f or from k to z.
Let us again try this with a program as above.
Sub wildcard_like_demo()
' declare a string variable and an input variable
Dim pattern1, var_input
' initialize the variable with [!P-X] in it.
pattern1 = "This is [!P-X] country"
' literally we have made pattern1 a pattern.
' receive an input from the user and store it in var_input variable
var_input = InputBox("Enter an expression . ")
'compare to see if there is any single character in the place of "[!P-X] that is within "
If var_input Like pattern1 Then
' if not, the input matches the pattern
Debug.Print "The input text '" & var_input & "' matches the pattern '" & pattern1 & "'"
Else
' if so, the input does not match the pattern
Debug.Print "The input text '" & var_input & "‘ does not match the pattern '" & pattern1 & "'"
End If
End Sub
The Output for Various Input Expressions:
The input text ‘this is my x country’ does not match the pattern ‘This is [!P-X] country’
The input text ‘This is N country’ matches the pattern ‘This is [!P-X] country’
The input text ‘This is Q country’ does not match the pattern ‘This is [!P-X] country’
The input text ‘This is % country’ matches the pattern ‘This is [!P-X] country’
The input text ‘This is 1 country’ matches the pattern ‘This is [!P-X] country’
The input text ‘This is any country’ does not match the pattern ‘This is [!P-X] country’
Conclusion
Several examples of this sort can be given for each wildcard character and a combination of those. But what we try to emphasize here is the use of the “Like“ keyword. It can be used to compare any expression with a created pattern that may/may not use wildcard characters.
For more information on wildcards and their combination, please look for related articles on our website.