What is Excel VBA Like Operator?
The VBA Like operator compares strings to determine if they match a specified pattern. It allows you to perform wildcard-based searches within strings, making it a powerful tool for string manipulation and text matching in VBA. This operator supports wildcard characters such as “?” and “*,” representing single and multiple characters.
Let us look at an example where we’ll use the VBA Like operator to validate email addresses. Here, we have a subroutine named “ValidateEmail” that aims to validate the format of an email address stored in the “email” variable. The condition checks if the “email” variable matches the pattern “@.,” where “” matches zero or more characters.
This pattern verifies that the email contains an “@” symbol and at least one dot (for the domain extension). If the condition is true, a message box displays “Email is valid!”; otherwise, it shows “Invalid email format.”
Table of Contents
Key Takeaways
- The VBA Like operator is used for pattern-based string matching. It supports wildcards like “?” and “*” representing single and multiple characters.
- The Like operator is case-insensitive by default. You can use brackets “[ ]” to specify character ranges and sets in patterns.
- Debug your code if the VBA Like operator is not behaving as expected, and consider using custom comparisons for case sensitivity.
- The VBA Like operator can be useful for tasks like data validation, text parsing, and filtering data based on patterns.
How To Use VBA Like Operator?
Using the VBA Like operator involves the following steps:
Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

Step 3: If your code requires variables, use the Dim statement to declare them. Variables are used to hold the values you want to compare.
For example:
Dim firstName As String
Dim lastName As String
Step 4: Within your VBA code, use the VBA Like operator to compare strings. Define the pattern you want to match, incorporating wildcard excel characters such as “?” (matches a single character) and “*” (matches zero or more characters).
For example:
If firstName Like “J*n” Then
Step 5: Typically, you use the VBA Like operator within conditional statements like If…Then…Else to determine whether the comparison results in a match or not.
For example:
If firstName Like “J*n” Then
MsgBox “First name matches the pattern!”
Else
MsgBox “First name does not match the pattern.”
End If
Step 6: Run your VBA code to execute the comparison using the VBA Like operator. Your code will produce the desired output or take specific actions depending on the condition.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Examples
Example #1 – With Question Mark
In this example, the “?” wildcard matches any single character, so “a?ple” matches “apple.”
Step 1: In the new module, first, we define a VBA subroutine named “Example1.”
Step 2: Next, we declare a string variable named “str” using the “Dim” statement.
Step 3: Here, we assign the string “apple” to the variable “str.”
Step 4: This line begins an “If” statement in VBA, which is used for conditional execution.
The condition being checked is whether the value of the “str” variable matches the pattern “a?ple.”
“a?ple” is a pattern where “?” represents any single character.
Step 5: If the condition in Step 4 is true (a match is found), this line displays a message box with the text “Match found!”
Step 6: If the condition in Step 4 is false (no match is found), this line indicates that the code should execute the following block of code (in this case, the MsgBox in the next step).
Step 7: If the condition in Step 4 is false, this line exhibits a message box containing the text “No match found!”
Step 8: Save the VBA macro and click on run.
When executed, the code will display a message box with the text “Match found!” because the string “apple” matches the pattern “a?ple” where “?” represents any single character.
Here is the full code:
Sub Example1()
Dim str As String
str = “apple”
If str Like “a?ple” Then
MsgBox “Match found!”
Else
MsgBox “No match found.”
End If
End Sub
Example #2 – With Asterisk
In this example, the “” wildcard matches zero or more characters. So, “bnana” matches “banana.”
Step 1: In the new module, we define a VBA subroutine named “Example2.”
Step 2: Subsequently, we utilize the “Dim” statement to declare a string variable called “str.”
Step 3: Here, we set the variable “str” to contain the string “banana.”
Step 4: This line initiates an “If” statement, which serves the purpose of executing code conditionally.
It assesses whether the content of the “str” variable conforms to the “bnana” pattern, where “” signifies the potential presence of zero or more characters.
Step 5: If the condition in Step 4 is true (a match is found), this line displays a message box with the text “Match found!”
Step 6: If the condition in Step 4 is false (no match is found), this line indicates that the code should execute the following block of code (in this case, the MsgBox in the next step).
Step 7: In the event that the condition in Step 4 is false, this line exhibits a message box containing the text “No match found!”
Step 8: Save the macro and run the code. When executed, the code will display a message box with the text “Match found!” because the string “banana” matches the pattern “bnana” where “” represents zero or more characters, allowing any characters to appear after “b.”
Here is the full code:
Sub Example2()
Dim str As String
str = “banana”
If str Like “b*nana” Then
MsgBox “Match found!”
Else
MsgBox “No match found.”
End If
End Sub
Example #3 – With Brackets []
In this example, “[1-5]” matches any single digit from 1 to 5, so “[1-5]2345” matches “12345.”
Step 1: In the new module, first, we define a VBA subroutine named “Example3.”
Step 2: We establish a string variable denoted as “str.”
Step 3: We allocate the string “12345” to the variable labeled as “str.”
Step 4: This line initiates an “If” statement featuring a specific condition.
This condition assesses if the content of “str” aligns with the “[1-5]2345” pattern, where “[1-5]” signifies any individual digit ranging from 1 to 5.
Step 5: When the condition in Step 4 holds true, this line exhibits a message box containing the text “Match found!”
Step 6: If the condition in Step 4 is false, this line indicates that the code should execute the following block of code (in this case, the MsgBox in VBA in the next step).
Step 7: This line displays a message box with the text “No match found!” if the condition in Step 4 is false.
Step 8: Save the macro and click on run.
When executed, the code will display a message box with the text “Match found!” because the string “12345” matches the pattern “[1-5]2345” where “[1-5]” represents any single digit from 1 to 5.
Here is the full code:
Sub Example3()
Dim str As String
str = “12345”
If str Like “[1-5]2345” Then
MsgBox “Match found!”
Else
MsgBox “No match found.”
End If
End Sub
Example #4 – With Brackets & Alphabets [A-Z]
In this example, “[A-Z]*” matches any string that starts with an uppercase letter. “Hello123” matches this pattern.
Step 1: In the new module, first, we define a VBA subroutine named “Example4.”
Step 2: We define a string variable named “str.”
Step 3: Here, we allocate the string “Hello123” to the variable named “str.”
Step 4: This line initiates an “If” statement incorporating a specific condition.
The condition evaluates if the content of “str” corresponds to the “[A-Z]” pattern, where “[A-Z]” signifies any uppercase letter, and “” denotes the potential presence of zero or more characters following the uppercase letter.
Step 5: If the condition in Step 4 is true, this line displays a message box with the text “Match found!”
Step 6: If the condition in Step 4 is false, this line indicates that the code should execute the following block of code (in this case, the MsgBox in the next step).
Step 7: This line displays a message box with the text “No match found!” if the condition in Step 4 is false.
Step 8: Save the macro and click on Run.
When executed, the code will display a message box with the text “Match found!” because the string “Hello123” matches the pattern “[A-Z]” where “[A-Z]” represents an uppercase letter, and “” matches zero or more characters after the uppercase letter.
Here is the full code:
Sub Example4()
Dim str As String
str = “Hello123”
If str Like “[A-Z]*” Then
MsgBox “Match found!”
Else
MsgBox “No match found.”
End If
End Sub
Important Things To Note
- By default, the VBA Like operator is case-insensitive in VBA. To make it case-sensitive, you can use a custom comparison function.
- If you want to match wildcard characters like “?” or “*”, you can use the tilde “” as an escape character. For example, “a?ple” would match “a?ple.”
- You can use multiple wildcards in a single pattern. For instance, “a*p?e” would match “apple.”
Frequently Asked Questions (FAQs)
By default, the VBA Like operator is not case-sensitive. It treats uppercase and lowercase letters as the same. If you need a case-sensitive comparison, you’ll need to implement custom logic in your code.
The VBA Like operator is used to compare strings against patterns. It is commonly used for wildcard-based searches and pattern matching within strings, making it a powerful tool for tasks like text parsing and data validation.
If the VBA Like operator is not working as expected, you should check for potential issues such as incorrect pattern syntax, missing wildcards, or case sensitivity. Debugging your code and verifying the input data can help identify and resolve the problem.
VBA Like Operator: It compares a string against a pattern using wildcards. It is useful for pattern-based matching.
InStr Function: It searches for one string within another and returns the position of the first occurrence. It is used for finding substrings within a larger string but does not support wildcards like the Like operator.
Recommended Articles
This has been a guide to VBA Like. Here we explain the how to use VBA Like Operator along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply