VBA Find: Check Whether a String Contains a Substring

In VBA, use InStr to check whether one string contains another: if the function returns a number greater than zero, the substring was found; if it returns zero, it was not. For a case-insensitive test, pass vbTextCompare as the comparison argument.

The basic InStr pattern

InStr searches a source string for a smaller string and returns the position where the match begins. This example checks whether the text in message contains “approved,” ignoring letter case:

Sub CheckForSubstring()
    Dim message As String
    Dim found As Boolean

    message = "The request is APPROVED."
    ' Compare without distinguishing uppercase and lowercase.
    found = (InStr(1, message, "approved", vbTextCompare) > 0)

    If found Then
        Debug.Print "Substring found"
    Else
        Debug.Print "Substring not found"
    End If
End Sub

Here, InStr returns a positive position because “APPROVED” occurs in the source text. The comparison with zero turns that numeric result into a Boolean value: True when found and False when absent. You can put the test directly in an If statement if you do not need to store the result first.

What InStr returns and how its arguments work

The general form is InStr([start, ]string1, string2, [compare]). The square brackets indicate optional arguments; they are not typed into the call. string1 is the text to search, and string2 is the substring to look for. If you supply start, the search begins at that character position. If you omit it, the search begins at position 1.

Positions are one-based: the first character is position 1, not position 0. For example, searching for “cat” in “a cat” finds the match at position 3. A return value of zero means there is no match. This makes the reliable Boolean test InStr(...) > 0; testing whether the result equals 1 would miss matches that begin later in the text.

The optional compare argument controls how VBA treats letter case and other textual differences. The two common constants are vbBinaryCompare and vbTextCompare. Use the named constants instead of unexplained numeric values such as 0 or 1: the code is easier to read, and the intent is explicit. The order of arguments matters, especially when you include a starting position.

Choose case-sensitive or case-insensitive matching

For a case-sensitive search, pass vbBinaryCompare. “Dog” and “dog” are then different strings, so searching for the second in the first returns zero. This can be useful when capitalization is meaningful in an identifier, code, or other exact text.

Sub CheckCaseSensitive()
    Dim sourceText As String
    Dim searchText As String

    sourceText = "Order ID: AbC-42"
    searchText = "abc"
    ' Binary comparison treats uppercase and lowercase as different.
    If InStr(1, sourceText, searchText, vbBinaryCompare) > 0 Then
        Debug.Print "Exact-case match found"
    Else
        Debug.Print "No exact-case match"
    End If
End Sub

For a case-insensitive search, use vbTextCompare. This is often the useful choice for ordinary words, where a cell containing “Error,” “ERROR,” or “error” should produce the same result. Text comparison can follow locale-specific rules, so it is not necessarily identical to comparing every character using a simple English-only rule.

You can also set a module-wide comparison behavior with an Option Compare statement at the top of a module. If you omit the compare argument, VBA uses the module’s setting; in a module without an Option Compare statement, the default is binary comparison. For code that must be clear regardless of module settings, pass vbTextCompare or vbBinaryCompare explicitly in the call.

Handle empty strings and other edge cases

An empty search string deserves special attention. When the substring argument is zero-length, InStr returns the starting position rather than zero. Consequently, a test for “found” can evaluate to true even though there was no text to search for. If an empty search value should count as no match in your program, check for it first.

Sub CheckNonEmptySearch()
    Dim sourceText As String
    Dim searchText As String

    sourceText = "Quarterly report"
    searchText = ""
    ' Treat a blank search value as not found.
    If Len(searchText) = 0 Then
        Debug.Print "Enter a search term"
    ElseIf InStr(1, sourceText, searchText, vbTextCompare) > 0 Then
        Debug.Print "Substring found"
    Else
        Debug.Print "Substring not found"
    End If
End Sub

Consider whether spaces should count as meaningful characters. A search value containing one space is not empty, and it will match spaces in the source. If users might enter accidental leading or trailing spaces, you can use Trim$ on their input before searching. Do this only when removing those spaces fits the requirements; whitespace can be meaningful in some data.

Excel cells can contain error values as well as text. If a macro reads a cell that might contain an error, do not assume it can safely be converted to a string. Check the value with IsError before applying a string function or converting it. Also decide how to treat blank cells and numbers: conversion may make a number searchable as its displayed text, but formatting and value types can affect what users expect.

Search worksheet cells with VBA

For a quick test against one known text value, assign the cell’s value to a variable and pass that variable to InStr. When a range may contain errors, check each value before converting it. This macro looks through A2:A20 on the active worksheet and writes matching cell addresses to the Immediate window:

Sub FindErrorTextInColumnA()
    Dim searchRange As Range
    Dim cell As Range
    Dim cellValue As Variant
    Dim searchText As String

    searchText = "error"
    Set searchRange = ActiveSheet.Range("A2:A20")

    ' Inspect each value, skipping cells that contain Excel errors.
    For Each cell In searchRange.Cells
        cellValue = cell.Value2
        If Not IsError(cellValue) Then
            If InStr(1, CStr(cellValue), searchText, vbTextCompare) > 0 Then
                Debug.Print cell.Address(False, False)
            End If
        End If
    Next cell
End Sub

Open the Visual Basic Editor with Alt+F11 in Excel for Windows. On a Mac, Option+F11 may work, sometimes with the Fn key depending on the keyboard; you can also use Excel’s Tools > Macro > Visual Basic Editor menu. Put the macro in a standard module, then run it from the editor or Excel’s macro dialog. The Immediate window is available in the editor through View > Immediate Window.

This example only reports matches; it does not change cells. Before running any macro that edits a workbook, save a separate copy first. VBA macros cannot be undone with Ctrl+Z, so a backup is important even if the code looks straightforward. If you want to highlight matches or change values, test that behavior on a copy and specify precisely which cells the macro is allowed to alter.

Common mistakes and troubleshooting

Checking for exactly 1: A match can start at any character position. Test whether the result is greater than zero.
Forgetting case behavior: A call with no comparison argument may use the module’s Option Compare setting. Specify a comparison mode when the result must be predictable.
Putting arguments in the wrong order: When using the start argument, the order is start, source string, search string, and comparison mode.
Searching for a word but finding part of one: InStr finds contiguous characters anywhere. Searching for “art” also matches “partial.” It does not identify word boundaries.
Passing an Excel error value: Test a cell value with IsError before treating it as ordinary text.
Unexpected blanks: A zero-length search string has special behavior, and a string containing spaces is not zero-length. Validate or trim user input according to the task.

When a search appears to fail, inspect the actual source and search strings in the Immediate window, including spaces and punctuation. A visually similar hyphen, apostrophe, or accented character may be a different character. If the text comes from imported data, hidden spaces or nonbreaking spaces can also make an apparently identical phrase fail to match. Normalize only the characters you intend to treat as equivalent.

InStr compared with worksheet functions and Like

Use InStr in VBA code, such as a macro or a user-defined procedure. If the goal is simply to display a result in a worksheet, Excel’s SEARCH and FIND functions may be more convenient. With the text to inspect in A2, this formula returns TRUE when A2 contains “cat,” without distinguishing case:

With the text to inspect in A2, enter =ISNUMBER(SEARCH("cat",A2)). For a value such as “A cat sleeps,” the result is TRUE. SEARCH is not case-sensitive; FIND is case-sensitive. Both return a position when they locate the text and an error when they do not, which is why ISNUMBER is useful for turning the result into TRUE or FALSE. These are worksheet formulas, not VBA statements.

VBA’s Like operator is another option when you need a pattern rather than a literal substring. For example, a pattern can use wildcard characters to match variable text. That flexibility changes the meaning of special characters, however, and the pattern syntax is not the same as simply searching for a literal phrase. Choose InStr for a straightforward contained-text check, and use Like only when pattern matching is actually required.

Frequently Asked Questions

Does InStr return True or False?

No. InStr returns a number: the match position, or zero when it finds no match. Convert that result into a Boolean test with InStr(...) > 0.

How do I make InStr ignore capitalization?

Pass vbTextCompare as the comparison argument, as in the examples above. This makes ordinary case differences irrelevant to the search.

How do I search only from a certain character?

Put the starting position first, followed by the source and search strings. For example, a start value of 5 begins the search at character five. A match entirely before that position will not be returned.

Can InStr find a whole word only?

Not by itself. It looks for a literal sequence anywhere, so it can match part of a longer word. To require word boundaries, add boundary checks or use a more deliberate pattern-matching approach appropriate to the text and language.

Should I use InStr or Find in Excel?

Use InStr in VBA when code needs a position or a yes-or-no substring test. Excel’s Find feature or worksheet functions are often better when a person is searching a sheet or when the result should remain visible in a cell. They serve different purposes and do not use identical interfaces or comparison settings.

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.