get-digital-help.com

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

like-operator.xlsm

---

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.