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 Comparestatement 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
CStrif needed, and test error values withIsErrorfirst. - 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
- How to Split an Excel Cell Into Two (and What Works)
- How to Space Down in Excel Using Indent
- How to Sort Google Sheets: Rows, Columns, and Filters
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Result: Return Values and Button ChoicesIn VBA, MsgBox returns a VbMsgBoxResult value that identifies which response button the user selected. Store that return…
- VBA Pivot Table: Add a Filter with CodeTo add a filter to an existing Excel PivotTable with VBA, set the relevant PivotField’s Orientation to xlPageField,…
- MS Access VBA Enum: Syntax, Examples, and Best PracticesAn Enum in Microsoft Access VBA is a named set of numeric constants. Declare it at the top…
- Add Controls to a VBA UserForm: Steps and CodeTo add controls to a VBA UserForm, open the form in the Visual Basic Editor and drag a…
- Macro Recorder Compatibility Across Excel VersionsExcel’s macro recorder creates VBA macros that work in desktop Excel for Windows and Mac, but compatibility is…
- Add a Hyperlink to a VBA MsgBox in ExcelYou cannot add a genuinely clickable hyperlink inside Excel VBA’s built-in MsgBox: it displays plain text and its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.