Excel VBA Like Statement: Patterns and Examples

VBA & macros · Updated November 20, 2026

In Excel VBA, the Like operator compares a string with a pattern and returns whether the string matches it. Use wildcard characters such as * and ? to test for flexible text patterns—for example, value Like "INV-####" checks for the exact format INV- followed by four digits.

How the Like operator works

Like is a VBA operator, not a built-in worksheet function. Its basic form is expression Like pattern. The expression is the text to inspect, and the pattern describes the allowed characters. The result is True when the expression matches the pattern and False when it does not.

The pattern applies to the entire expression, not just a portion of it. For instance, "cat" Like "c*t" is True, but "a cat" Like "c*t" is False because the expression begins with additional characters. To allow extra characters before and after a target, put a star at both ends: "a cat" Like "*cat*".

This makes Like useful for validation and branching logic in macros. It can check whether a cell contains an ID in a particular format, whether a filename has a given extension, or whether text begins with a specified prefix. It does not extract a matching substring or replace text; it only tests the match.

VBA Like pattern characters

VBA has several pattern characters. Use them to express what can appear at each position in the string:

  • * matches zero or more characters. For example, "report*" matches "report", "reports", and "report 2026".
  • ? matches exactly one character. The pattern "A?C" matches "ABC" or "A7C", but not "AC".
  • # matches exactly one digit. The pattern "###" matches a three-digit string such as "407", but not "40" or "4A7".
  • A character list in square brackets matches one character from the list. For example, "[ABC]at" matches "Bat" but not "Cat"? It does match both, as well as "Aat".
  • A range inside brackets matches one character in the range. For example, "[A-Z]##" describes one letter followed by two digits.
  • An exclamation mark at the start of a bracketed list means “not these characters.” For example, "[!0-9]*" requires the first character to be something other than a digit, followed by any number of characters.

Patterns can combine these symbols. In "AB-##??", the text must start with AB-, then have two digits and then exactly two more characters. Because * can match zero characters, "file*.xlsx" also matches "file.xlsx", where the star accounts for nothing.

Examples for common checks

Here are some patterns and what they test. Each comparison checks the complete string unless a wildcard permits additional characters:

  • "INV-2841" Like "INV-####" returns True: the prefix is exact and the four remaining characters are digits.
  • "INV-28A1" Like "INV-####" returns False: the letter A does not match #.
  • "sales_q4.xlsx" Like "*.xlsx" returns True because the string ends in the specified extension.
  • "North Region" Like "North*" returns True because the string starts with North.
  • "A7C" Like "A?C" returns True, while "AC" Like "A?C" returns False because the question mark needs one character.
  • "Room 12" Like "*Room*" returns True because the pattern permits text on both sides of Room.

When a pattern includes a character that is normally a wildcard, use bracket syntax to treat it as a literal. For example, "Price*" Like "Price[*]" tests for the literal text Price*, rather than using the star to stand for any characters. This is useful when validating filenames or labels that may contain wildcard symbols.

Using Like in an Excel VBA macro

The following example reads codes in column A, starting at A2, and writes a status in column B. It accepts only codes in the format INV- followed by four digits. It assumes the worksheet is named Sheet1 and that the input cells contain text or blanks. The module-level setting makes the comparison case-insensitive; remove that line if you want the module’s default binary comparison.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, and this example writes results into column B.

Option Compare Text

Sub CheckInvoiceCodes()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    ' Read codes from Sheet1 and write results beside them.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    For Each cell In ws.Range("A2:A" & lastRow)
        If IsError(cell.Value) Then
            cell.Offset(0, 1).Value = "Check"
        ElseIf CStr(cell.Value2) Like "INV-####" Then
            cell.Offset(0, 1).Value = "Valid format"
        Else
            cell.Offset(0, 1).Value = "Check"
        End If
    Next cell
End Sub

The code checks format, not business validity: a value such as INV-0000 matches even if that code is not meaningful in your process. If the list is in another column or the sheet has a different name, adjust the references. The IsError check avoids trying to convert an Excel error value to text.

Case sensitivity and Option Compare

Whether letters match regardless of case depends on the comparison setting in the VBA module containing the code. In Excel VBA, the default is Option Compare Binary, which compares text using binary character values and treats uppercase and lowercase letters as different. With that default, "abc" Like "ABC" is False.

You can put Option Compare Text at the top of a module, before any procedures, to make string comparisons in that module—including Like comparisons—case-insensitive. This setting applies to the module, not just one line of code. It follows text-comparison rules that can be affected by the system locale, so use it deliberately when case-insensitive matching is required.

If only one comparison needs a different case treatment, converting both values to the same case before comparing is another option. For example, compare LCase$(value) with a lowercase pattern. This can make the intent visible at the comparison itself, though the module’s comparison rules still apply to the resulting strings.

Like compared with Excel wildcards and regular expressions

VBA Like and Excel worksheet wildcards look similar, but their pattern rules are not identical. In worksheet functions and features that accept criteria, * and ? are wildcards, and a tilde is used to escape them. VBA Like instead supports # for a digit and bracketed character lists such as [A-Z] or [!0-9]. Do not assume that a pattern written for a worksheet criterion can be copied unchanged into VBA.

Like is also not a regular-expression engine. It does not provide regex features such as capture groups, repetition counts in braces, or alternation using a vertical bar. For straightforward checks involving fixed text, any-character wildcards, digits, or character lists, Like is compact and readable. For complex text parsing, consider a suitable regular-expression solution or explicit VBA string operations instead.

It is not a native formula you can type directly into a cell. VBA code can use it inside a macro, and a custom VBA function could expose a test to a worksheet, but that function would require VBA and macro-enabled workbook support. If you only need ordinary worksheet filtering or criteria matching, Excel’s built-in functions and filter tools may be simpler.

Common mistakes and troubleshooting

  • Expecting a substring match without stars: "abc" Like "b" is False. Use "*b*" when the target can occur anywhere in the text.
  • Using the wrong digit symbol: In VBA Like, # means one digit. A question mark means any one character, including a letter or punctuation mark.
  • Allowing too many characters: A star can match zero or many characters. If a code must have a fixed length, prefer ? or # at each required position rather than a broad star.
  • Forgetting case settings: A pattern that appears correct can fail because the module uses binary comparison. Check for an Option Compare statement at the top of the module.
  • Passing non-text values: When cells may contain numbers or errors, consider how they should be handled. Convert ordinary values with CStr if needed, and test error values with IsError first.
  • Assuming a match proves validity: A pattern only checks the shape of the string. It cannot determine whether a code exists in a master list, is unique, or meets rules that are not represented in the pattern.

For debugging, test a few known examples separately, including one that should match and one that should fail. If a long pattern is difficult to understand, break the validation into smaller checks—for example, test the prefix, length, and numeric portion independently. That can make the reason for a failed match easier to locate.

When to choose Like

Use Like when VBA needs a concise yes-or-no test against a simple pattern. Typical uses include categorizing rows by code prefix, screening filenames, validating a fixed-format identifier, and directing a macro with If...Then logic. It is especially clear when the allowed characters are easy to describe position by position.

Choose another approach when you need to return the matching portion, replace text, support complicated validation rules, or compare against a list of permitted values. A lookup or a more explicit sequence of string checks may better express those requirements. For identifiers with a fixed total length, you may also combine Like with a length check so the rule is obvious to someone maintaining the macro.

Frequently Asked Questions

Can Like find a word anywhere in a cell value?
Yes. In VBA, surround the word with stars in the pattern, such as "*urgent*". The stars allow any characters before and after the target. Without them, the pattern is tested against the whole string.

Is Like case-sensitive in Excel VBA?
It depends on the module’s Option Compare setting. The default binary comparison is case-sensitive; Option Compare Text makes text comparisons in that module case-insensitive.

What does # mean in a VBA Like pattern?
# matches exactly one digit. For example, "###" matches a three-digit string. It does not mean any character; use ? for one character of any kind.

Can I use Like directly in a worksheet formula?
No. Like is a VBA operator, not a built-in worksheet formula function. You can write VBA code or a custom VBA function that uses it, but that requires a workbook with VBA support and appropriate macro settings.

Is VBA Like the same as regular expressions?
No. It supports a smaller pattern syntax, including *, ?, #, and bracketed character lists. Use a regex approach when you need features that Like does not provide, such as capture groups or complex alternatives.

Related

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.