The VBA Like operator allows you to compare strings against a pattern. You can search a string to check if it contains another string, or you can check if a string conforms to a certain format.
By searching if a string contains another string, Like does a similar job to the InStr string function, but whereas Like returns a True or False result, InStr returns the position of where one string is in the other.
Pattern Matching
You can use wildcards, character lists and character ranges, in any combination, to create a pattern. The following table illustrates these patterns and what they match.
| Pattern | Matches | Example | String | Match |
| ? | A single character | d?g | dog | True |
| dig | True | |||
| drag | False | |||
| * | Zero or more characters | d*g | dog | True |
| d*g | dg | True | ||
| d*g | dragon | False | ||
| # | Any single digit 0 to 9 | ### | 999 | True |
| ### | 00 | False | ||
| [chars] | Any single character in the list chars | [ab][ab][ab] | aba | True |
| [ab][ab][ab] | bab | True | ||
| [ab][ab][ab] | cba | False | ||
| [char-char] | Any single character in the range char-char | [a-c][d-f][g-i] | beh | True |
| [a-c][d-f][g-i] | cfg | True | ||
| [a-c][d-f][g-i] | dei | False | ||
| [!chars] | Any single character not in the list chars | [!ab][!ab][!ab] | ccc | True |
| [!ab][!ab][!ab] | abc | False | ||
| [!char-char] | Any single character not in the range char-char | [!a-c][!d-f][!g-i] | yyz | True |
| [!a-c][!d-f][!g-i] | mkb | True | ||
| [!a-c][!d-f][!g-i] | pgi | False |
Character Ranges
When you use a character range, the characters must appear in ascending sort order e.g. [a-z] not [z-a].
Multiple Ranges
You can specify more than one range for a character position by including them all in the same square brackets e.g. [a-cx-z] would match a, b, c, x, y, z.
Comparison Method
The way Like behaves is dependent on Option Compare.
By default Option Compare is set to Binary, which means the binary representation for a character is used to compare it against another. Effectively this means that pattern matching is case sensitive.
If you want to do case-insensitive comparisons, then at the top of your VBA module you need to have the statement Option Compare Text
IsLike Function
If you look in the sample workbook I created you'll see I've written the function IsLike
This returns either True or False depending on whether your pattern matches the string. The function can be altered to return something other than True/False of course, if you would find that more useful.
Using Like to Check the Formatting of a String
Not only does Like allow you to match against the contents of the string, it allows you to match the format. For example you might want to check that a phone number has been entered using the correct international format e.g. +Country_Code <Phone Number>
If you wanted to call an Australian land line number you would need to dial +61 x xxxx xxxx. The pattern for this is +61 # #### ####
Download the Workbook
Enter your email address below to download the sample workbook.
A slight improvement on the check for valid email would look like this: =AND(islike(A10,”*@*”),islike(A10,”*.*”))
Hi Gayle, no need to all the function twice for that,
=islike(A10,”*@*.*”) will do the same in one call.
We can use a second call to identify if the text contains any of the illegal chars in an email address.