WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Like

VBA Like

VBA Like Operator

Like is an operator in VBA and this is a comparison operator which compares a given string as argument in a set of strings and it matches the pattern, if the pattern is matched then the result obtained is true and if the pattern does not match then the result obtained is false, this is an inbuilt operator in VBA.

“LIKE” operator is the most underused operator despite its wonderful usage. I have not seen many people who use this operator to a full extent in their coding. In fact, I am one of them who doesn’t use this operator quite often. “VBA LIKE” operator allows us to match the pattern of the string against the full string. By using VBA LIKE operator, we can compare two strings against the patter given. We can check whether the string contains a substring in VBA, or we can also whether the string contains any specific format. If the pattern matches with the string, then VBA LIKE operator returns TRUE or else FALSE.

While matching strings, we need to use wildcard characters to the patter we specify. Below are the wildcards we use in VBA LIKE operator.

  • 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.
  • 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.
  • Brackets ([]): This matches any one single character specified in the brackets.
  • [Char-Char]: This matches any single character in the range Char-Char.
  • [!Chars]: This matches any single character not in the list.
  • [!Char-Char]: This matches any single character not in the range Char-Char.

VBA Like Operator

Examples of VBA LIKE Operator

Let’s see some of the examples of VBA LIKE operator now.

You can download this VBA Like Excel Template here – VBA Like Excel Template

Example #1 – With Question Mark

Code:

Sub QuestionMark_Example1()

  Dim k As String
  k = "Good"

  If k Like "Go?d" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

End Sub

In the above code, we have supplied the string as “Good,” and the pattern is “Go?d.” Since the question mark can match a single character, it will show the result as “Yes.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA LIKE Example 1

Now I will change the string to “Good Morning.”

Code:

Sub QuestionMark_Example1()

  Dim  k As String
  k = "Good Morning"

  If k Like "Go?d" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

End Sub

In this case, it will show “No” because we have added one more word to the string, i.e., Morning. To match any number of characters, we need to use the asterisk.

VBA LIKE Example 1-1

Example #2 – With Asterisk

Code:

Sub QuestionMark_Example2()

  Dim k As String
  k = "Good Morning"

  If k Like "*Good*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

End Sub

In the above example, I have added two asterisks before and after the character “*Good*.” This will match the word “Good” in the string “Good Morning” and returns “Yes.”

VBA LIKE Example 2

Example #3 – With Brackets []

Code:

Sub QuestionMark_Example3()

  Dim k As String
  k = "Good Morning"

  If k Like "*[M]*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

End Sub

The above code matches the single letter mentioned in the bracket “M” and returns the result as Yes.

VBA LIKE Example 3

Example #4 – With Brackets & Alphabets [A-Z]

Code:

Sub QuestionMark_Example4()

  Dim k As String
  k = "Good Morning"

  If k Like "*[A-D]*" Then
   MsgBox "Yes"
  Else
   MsgBox "No"
  End If

End Sub

In the above, I have mentioned the characters to match from A to D.

This will return “No” because there are no characters from A to D in the string “Good Morning.”

Brackets & Alphabets Example 4

Now I will change the pattern to [A-H]

Code:

Sub QuestionMark_Example4()

  Dim k As String
  k = "Good Morning"

  If k Like "*[A-H]*" Then
    MsgBox "Yes"
  Else
    MsgBox "No"
  End If

End Sub

This will return “Yes” because from A to H, we have a character “G” in the string “Good Morning.”

Brackets & Alphabets Example 4-1

Like this, we can use VBA “LIKE” operator to match any string from the pattern with wild card characters.

Recommended Articles

This has been a guide to VBA LIKE. Here we will take through how to use VBA LIKE operator using a question mark, Asterisk, and Brackets & Alphabets along with examples and download an excel template. You may also have a look at other articles related to Excel VBA –

  • Excel VBA CStr Function
  • Boolean in VBA
  • Right Function in VBA
  • For Each Loop in VBA
  • VBA ISNULL
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Like Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More