Excel VBA Like Operator: Pattern Matching Explained

Yes. In Excel VBA, the Like operator compares a string with a pattern, letting you test for text such as an invoice code with four digits: codeValue Like "INV-####". Its wildcards and character sets are useful for validation and filtering, but Like is VBA syntax—not a built-in worksheet formula.

How the VBA Like operator works

The basic form is string Like pattern. VBA evaluates the comparison as True or False, so you can use it in an If statement, assign the result to a Boolean variable, or return it from a function. The pattern describes the string being tested; it is not a regular expression and does not search for a match anywhere inside the string by default.

For example, "Report.xlsx" Like "*.xlsx" is true because the pattern allows any text before the literal extension. By contrast, "old Report.xlsx" Like "Report.xlsx" is false: without a wildcard, the whole string must match. To look for a phrase anywhere in a value, surround it with asterisks, as in textValue Like "*Report*".

A pattern can mix ordinary characters and special pattern characters. The ordinary characters must match the corresponding text, while each wildcard controls what can appear at its position. The result is a Boolean comparison, not a replacement or extraction operation; use other VBA tools if you need to change or pull text out of a string.

VBA Like wildcard characters

These are the main pattern characters used by Like:

? matches exactly one character. "cat" Like "c?t" is true, while "ct" Like "c?t" is false.
* matches zero or more characters. Thus, "Book" Like "B*k" is true, and so is "Bk" Like "B*k".
# matches one digit from 0 through 9. For example, "A7" Like "A#" is true, but "A72" Like "A#" is false because the pattern expects exactly two characters.
[charlist] matches one character from the listed set. "m" Like "[aeiou]" is false, while "a" Like "[aeiou]" is true.
[!charlist] matches one character that is not in the listed set. For example, "Q" Like "[!A-Z]" is false because Q is in the range; a digit such as "7" matches.

Character lists can include ranges, such as [A-Z] or [0-9], and individual characters, such as [ABC]. A range must be written in ascending order. A bracket expression represents one character, not a variable-length choice: [A-Z]## describes one letter followed by two digits.

To match a special character literally, put it in a bracket expression. For instance, [*] matches an asterisk and [?] matches a question mark. The closing bracket has special handling in bracket expressions, so patterns that need to match literal brackets deserve a small test with representative input before being used on important data.

Practical VBA examples

A common use is checking whether a value follows a simple code format. This example reads a value from cell A2 on a sheet named Data and displays whether it matches the pattern. It accepts the exact prefix INV-, followed by four digits; the pattern does not accept extra characters before or after the code.

Option Compare Text
Option Explicit

Sub CheckInvoiceCode()
    Dim codeValue As String
    Dim isValid As Boolean

    ' Read the value to check from the Data worksheet.
    codeValue = CStr(ThisWorkbook.Worksheets("Data").Range("A2").Value)
    isValid = (codeValue Like "INV-####")

    If isValid Then
        MsgBox "The invoice code matches the pattern."
    Else
        MsgBox "The invoice code does not match the pattern."
    End If
End Sub

Here, Option Compare Text makes text comparisons in this module case-insensitive, so a lowercase prefix can match. If case must be enforced, use binary comparison instead, as explained below. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z, even when a particular example only reads a cell.

Another pattern can find names beginning with a particular letter: personName Like "J*" is true for Jordan and for J, because the asterisk can match zero or more characters. To check that a value contains a hyphen and then a single digit at the end, use a pattern such as *-#. Remember that the leading asterisk allows any preceding text, including none.

Case sensitivity and Option Compare

Whether letter matching is case-sensitive depends on the module’s comparison setting. With the default Option Compare Binary, comparisons distinguish uppercase and lowercase letters. With Option Compare Text, they do not. For example, under binary comparison, "abc" Like "ABC" is false; under text comparison, it is true.

Put an Option Compare statement at the top of the VBA module, before procedures. If no comparison option is specified, VBA uses binary comparison. The setting applies to string comparisons in that module, not just to one Like expression, so changing it can affect other code there. Choose deliberately when case has meaning, such as for identifiers that must preserve capitalization.

For a one-off case-insensitive test in a module that otherwise uses binary comparison, normalize both strings before comparing, for example with UCase$. That makes the intention visible at the expression itself, though it can be less convenient for patterns containing letter ranges. Test any range patterns with the characters and locale your workbook actually uses.

Using Like from an Excel worksheet

You cannot type VBA’s Like operator directly into a normal Excel cell formula. If worksheet users need the test, put a public VBA function in a standard module and call it as a user-defined function. This version uses the module’s comparison setting; add Option Compare Text at the top of that module if matching should ignore letter case.

Option Explicit

Public Function MatchesLike(ByVal textToCheck As String, _
                            ByVal pattern As String) As Boolean
    ' Return the VBA Like comparison result.
    MatchesLike = (textToCheck Like pattern)
End Function

With the function saved in a macro-enabled workbook, put the text INV-4821 in A2; enter this formula in B2: =MatchesLike(A2,"INV-####"). The result is TRUE, because the value has the INV- prefix followed by four digits. The cell formula calls the VBA function; Like itself is still not a worksheet operator.

Users may need to enable macros for the function to calculate. Excel for the web can open a workbook containing VBA, but it cannot run or edit VBA macros. Excel desktop for Windows and Mac supports VBA, although menus and some VBA capabilities can differ between platforms. If a workbook must work without macros, use a worksheet formula or another non-VBA approach instead.

Common mistakes and limits

Expecting a substring search: ABC Like "B" is false. Use *B* when the pattern should match a B anywhere in the string.
Confusing # with *: # represents one digit, not any character and not any number of digits. Use #### for exactly four digits, or a different approach if the digit count can vary.
Assuming a pattern validates every business rule: INV-#### checks a text shape, not whether an invoice exists, whether its number is permitted, or whether the value was originally entered as text.
Ignoring extra spaces: a trailing space can make an otherwise plausible comparison fail. If spaces should be disregarded, trim the value intentionally, for example with Trim$, before testing.
Assuming all wildcard systems are the same: Excel functions such as COUNTIF use their own wildcard rules. In those worksheet criteria, * and ? are wildcards and ~ escapes them; VBA Like also has # and bracket character lists.

For values read from cells, consider what happens when a cell contains an error, a number, or a date before converting it to a string. The sample macro is intentionally small; production code may need to check the cell value’s type and handle blank or error values explicitly. Like is a pattern comparison, not a complete data-cleaning or validation framework.

Like compared with other matching methods

Choose Like when the rule is readable as a compact wildcard pattern: a prefix, a fixed number of digits, a single character from a set, or a simple combination. It is built into VBA and needs no separate library reference. Its concise syntax is especially handy in an If condition or in a loop that checks many strings.

Use InStr when the question is simply whether a particular substring occurs, or use StrComp when you need an explicit string comparison mode. For more elaborate rules—such as optional groups, repeated alternatives, or extracting captured sections—a regular expression can be a better fit, but it has different syntax and setup considerations. Do not treat a Like pattern as a regular expression: for example, # means a digit in VBA Like, while regular-expression syntax uses other conventions.

For purely worksheet-based work, Excel’s text functions and wildcard-enabled criteria may be more portable than a VBA user-defined function. Pick the method based on where the logic needs to run, how complex the pattern is, and whether macros are allowed—not just on which syntax looks shortest.

Frequently Asked Questions

Is Like a VBA function?
No. Like is a VBA operator. It compares a string against a pattern and returns True or False; it is not called with function-style parentheses.

How do I match any number of characters in VBA?
Use an asterisk. For example, "Sales*" matches text starting with Sales, including the word Sales by itself. Put an asterisk at each side, as in "*Sales*", to allow text before and after.

How do I match exactly four digits?
Use four number-sign wildcards: "####". To require a prefix as well, a pattern such as "ID-####" matches ID-, then exactly four digits, with no extra characters.

Is VBA Like case-sensitive?
It depends on Option Compare in the module. Binary comparison, the default, distinguishes case; text comparison ignores case. Check the module declaration rather than assuming the setting from another procedure or workbook.

Can I use Like in a cell formula?
Not directly. You can expose a VBA comparison through a user-defined function, but that requires a macro-enabled workbook and desktop Excel to run. If macros are unavailable, use suitable worksheet functions or criteria instead.

Does Like find text inside a longer string?
Not unless the pattern allows surrounding characters. A pattern such as "*word*" permits text before and after the word; without those asterisks, the whole string must satisfy the pattern.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.