Working with the LIKE OPERATOR
The LIKE operator allows you to match a string to a pattern in excel vba. The following characters are specifically designed to assist you in building a pattern:
? (question mark) - Matches any single character
* (asterisk) - Matches zero or more characters
# (number or hash sign) - Any single digit
A1A* - You can also use a string combined with characters above to build a pattern. This matches a string beginning with or equals A1A.
[abc] - Characters enclosed in brackets allows you to match any single character in the string.
[!abc] - The exclamation mark (!) matches any single character not in the string.
[A-Z] - The hyphen lets you specify a range of characters.
Add Option compare binary or Option compare text before any macros or custom functions in you code module to change how string comparisons are made.
The default setting is Option compare binary. Use Option compare text to make the comparison case-insensitive but put the code in a separate module so other macros/functions are not affected.
To learn more, read this article: Option Compare Statement
The LIKE operator returns a boolean value, TRUE or FALSE depending on if the pattern is a match or not.
Compare a cell value to a pattern
This simple custom function lets you specify a pattern and compare it to a cell value. If there is a match, the function returns TRUE. If not, FALSE.
Function Compare(c As Range, pttrn As String) As Boolean Compare = c Like pttrn End Function
Copy the code above and paste it to a code module in the VB Editor, if you want to use it.
Question mark (?) examples
The picture below demonstrates the custom function above. It takes the string in cell A2 and compares it to the pattern in cell B2.
A question mark (?) matches any single character.
Value in cell A2 ABC matches A?C, TRUE is returned in cell D2.
Value in cell A3 ABCD does not match pattern A?D. BC are two characters, a question mark matches any single character. FALSE is returned in cell D3.
Value ABCD matches ?BC? and TRUE is returned in cell D4.
Asterisk (*) examples
The pattern tells you that the first three characters must be AAA and then the * (asterisk) matches zero or more characters. AAAC is a match to pattern AAA* and the custom function returns TRUE in cell D2.
aaa* does not match pattern AAAC. aaa is not equal to AAA. LIKE operator is case sensitive unless you change settings to Option Compare Text.
(*) matches zero or more characters, DDC23E matches DD*E.
Number sign (#) examples
# matches a single digit. To match multiple digits use multiple #.
123 matches 12#, TRUE is returned in cell D2.
123 does not match 1#, number sign matches any single digit.
123 matches #2#.
Combining characters
The following three examples use asterisks, question marks and number signs combined.
Brackets
Remember brackets match any single character you specify. A hyphen lets you compare a range of characters.
Search for a pattern and extract matching values
The following custom function allows you to extract cell values using the LIKE operator.
Array fomula in cell C2:C6:
Function SearchPattern(c As Range, pttrn As String) Dim d as String For Each cell In c If cell Like pttrn Then d = d & cell & "," Next cell SearchPattern = Application.Transpose(Split(d, ",")) End Function
Search for a a pattern and return values in an adjacent column
This custom function allows you to look for a pattern in a column and return the corresponding value in another column.
Array fomula in cell D2:D6:
Function SearchCol(b As Range, c As Range, pttrn As String) Dim a As Long, d as String a = b.Cells.CountLarge For i = 1 To a If b.Cells(i) Like pttrn Then d = d & c.Cells(i) & "," Next i SearchCol = Application.Transpose(Split(d, ",")) End Function
Did you know?
You can use the question (?) mark and asterisk (*) in many excel functions.
If you need to use even more complicated patterns excel allows you to use regular expressions, see this thread:
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
Download excel *.xlsm file
Extract cell references from a formula
I am trying to build a regular expression that matches cell references in a formula. A regular expression is a […]
Count matching strings using regular expressions
A regular expression is a pattern containing specific characters to search for sub-strings in strings. The patterns are at first […]
Fetching values from ThingSpeak using vba
Thingspeak is web service that allows you to upload and store data from IoT devices. Picture above shows cheap chinese […]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form