How to Use Excel VBA Like Operator (7 Suitable Examples)

Excel VBA Like operator is a very useful feature that allows you to match one or more specific letters or numbers within a string. In this article, we will walk you through seven examples of using Excel VBA Like operator with proper illustrations.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Introduction to VBA Like Operator

Like is a built-in operator in Excel VBA. It is used to match patterns in string. It allows users to compare strings with a pattern that may use wildcard characters to find matches. The syntax of the Like operator is as follows.

String_expression  Like  Pattern

i. Wildcard or Pattern Characters

ii. Make Like Operator Case Insensitive

Type Option Compare Text before writing the VBA code to make the Like operator case insensitive.

How to Launch VBA Editor in Excel

To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic. You can also open it by pressing Alt+F11 on your keyboard.

Then go to the Insert tab and click on Module to open the code Module.

Excel VBA Like Operator: 7 Suitable Examples

In this article, we will demonstrate seven easy examples of using the Like operator in Excel VBA. The examples are described in the following section.

1. Use Asterisk with Excel VBA Like Operator to Match Strings

First, we will use the asterisk (*) with the Like operator to match strings. In the following example, we have used “*you*” in the like operator. As a result, it will match the word “you” in the string “Thank you” and return Yes.

Sub LikeAsterisk()
  Dim text As String
  text = "Thank you"
  If text Like "*you*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "Thank you"
  If text Like "*you*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

2. Excel VBA Like Operator with Question Mark to Match Characters

In this example, you will see the use of Excel VBA Like operator with question mark (?) to match characters. Here, we have used “Tha?ks” in the Like operator. Any six-letter word having the first three letters “Tha” and the last two letters “ks” will return Yes when you run the following code.

Sub LikeQuestionMark()
  Dim text As String
  text = "Thanks"
  If text Like "Tha?ks" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "Thanks"
  If text Like "Tha?ks" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

3. Using Brackets with Like Operator

In this example, we will use a wildcard character (Brackets) with the Like operator. We have used “*[k]*” in the Like operator. As a result, if the letter “k” is present anywhere in the string “Thank you”, the result will be Yes. You can run the following code to see it for yourself.

Sub LikeBrackets()
  Dim text As String
  text = "Thank you"
  If text Like "*[k]*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

  text = "Thank you"
  If text Like "*[k]*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

4. Use Alphabets and Brackets with Like Operator

We can also use both the brackets [] and the range of the alphabet together with the Like operator. If the string contains any of the letters of the specified alphabet, the MsgBox will display Yes.

Sub LikeAlphabetsBrackets()
  Dim text As String
  text = "Thank you"
  If text Like "*[c-j]*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "Thank you"
  If text Like "*[c-j]*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

5. Excel VBA Like Operator with “#” Wildcard Character to Match a Single Digit

In this example, you will learn to match a single digit using the Excel VBA Like operator with “#” wildcard character. Here, we have used “Tha#ks” in the following code. If the string contains a number in the place of “#”, the MsgBox will display Yes.

Sub LikeHash()
  Dim text As String
  text = "Tha5ks"
  If text Like "Tha#ks" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "Tha5ks"
  If text Like "Tha#ks" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

6. Matching with Like Operator

We can match one or more letters or numbers in a string by using Excel VBA Like operator. Moreover, we can match phone numbers separated by “.“or “–“using the Like operator.

6.1 Match Uppercase Character

First, we will match an uppercase character with the help of the Like operator. We will use the following code for this purpose.

Sub MatchUppercase()
  Dim text As String
  text = "THANKS"
  If text Like "THA[A-Z]KS" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

Press F5 on your keyboard to run the code. If the string includes an uppercase character in the position of [A-Z], the output will be Yes.

 text = "THANKS"
  If text Like "THA[A-Z]KS" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

6.2 Not Matching Uppercase Character

If you don’t want the uppercase letters to match, you can simply put [!A-Z] instead of [A-Z]. Now if you run the code, it will display No as it includes an uppercase character.

Sub NotMatchUppercase()
  Dim text As String
  text = "THANKS"
  If text Like "THA[!A-Z]KS" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

  text = "THANKS"
  If text Like "THA[!A-Z]KS" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

6.3 Matching Any Character (Uppercase or Lowercase)

If you want to match a character regardless of whether it is uppercase or lowercase, you can use “[A-Za-z]” in the Like operator. The following code will show Yes if the string contains the letters in uppercase or lowercase.

Sub MatchUppercaseLowecase()
  Dim text As String
  text = "Thanks"
  If text Like "[A-Za-z]ha[A-Za-z]ks" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "Thanks"
  If text Like "[A-Za-z]ha[A-Za-z]ks" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

6.4 Match a Phone Number

In this example, we will match a phone number using Excel VBA Like Operator. The following code will match a 10-digit phone number separated by “.” or “–”.

Sub MatchPhoneNumber()
  Dim text As String
  text = "123-345-6789"
  If text Like "###[-.]###[-.]####" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "123-345-6789"
  If text Like "###[-.]###[-.]####" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

7. Search Regex Pattern and Extract Matching Values Using Like Operator

In this example, we will create a user-defined function (UDF) that will extract cell values based on a specified pattern. The function is explained below.

Function Pattern(r As Range, p As String)
Dim d As String
For Each cell In r
If cell Like p Then d = d & cell & ","
Next cell
Pattern = Application.Transpose(Split(d, ","))
End Function

For Each cell In r
If cell Like p Then d = d & cell & ","
Next cell
Pattern = Application.Transpose(Split(d, ","))

After writing the function, go to the Excel worksheet and type the following formula in cell D5.

Press Enter and get your desired output.

How to Use VBA Not Like Operator in Excel

In this example, we will use the VBA Not Like operator. In this case, if the string includes the specified characters, the MsgBox will display No.

Sub NotLike()
  Dim text As String
  text = "Thank you"
  If Not text Like "*you*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If
End Sub

text = "Thank you"
  If Not text Like "*you*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

Frequently Asked Questions

• What is the difference between InStr and Like in VBA?

The Like operator and the Instr function both searches for a string or character within another string. However, the Like operator returns True or False as output whereas, the Instr function returns the position of one string or character in the other.

• What is the purpose of the “Like” operator in Excel VBA?

The Like operator is used to compare strings with patterns. If the string matches the pattern, it returns True, otherwise, it returns False.

• Can the “Like” operator be used to compare numerical values in Excel VBA?

The like operator can not be used to compare numerical values. It is only used to compare strings with specific patterns.

Things to Remember

• Don’t forget to use proper wildcard characters otherwise, you won’t get the desired results.
• Write Option Compare Text before each VBA code to make the Like operator case insensitive.

Conclusion

Thanks for making it this far. I hope you find this article helpful. In this article, we have demonstrated seven easy examples of using Excel VBA Like operator. We have used the Like operator with different wildcard characters such as asterisks (*), question marks (?), brackets [] etc. We have also covered matching different strings, phone numbers, and patterns using the operator. If you have any queries regarding this article, feel free to let us know in the comment section below.