Excel VBA: Check If a String Contains a Substring in a Cell

In Excel VBA, use InStr to check whether one string contains another: it returns a position greater than zero when it finds a match, and zero when it does not. For a straightforward yes-or-no test, compare the result with zero; pass vbTextCompare if the match should ignore letter case.

Basic InStr check

InStr is VBA’s standard function for searching for a substring inside a string. Its simplest form is InStr(string1, string2), where string1 is the text to search and string2 is the text you are looking for. The return value is a character position, counted from 1, rather than a Boolean value. A result of 0 means no match was found.

This small procedure shows the essential pattern. It searches the full text for the word “invoice” and reports the result in the Immediate window in the Visual Basic Editor:

Sub CheckSubstring()
    Dim fullText As String
    Dim needle As String
    Dim foundAt As Long

    fullText = "Please review the invoice today."
    needle = "invoice"

    ' InStr returns zero if needle is absent.
    foundAt = InStr(1, fullText, needle, vbTextCompare)

    If foundAt > 0 Then
        Debug.Print "Match at character " & foundAt
    Else
        Debug.Print "No match"
    End If
End Sub

The first argument, 1, tells VBA to begin at the first character. In this example the result is 19, because “invoice” starts at position 19 in the sample sentence. To get only True or False, use the same test in an expression: InStr(1, fullText, needle, vbTextCompare) > 0.

Choose whether the search is case-sensitive

Case handling is an important choice. vbTextCompare makes the search case-insensitive, so “Invoice,” “INVOICE,” and “invoice” all match. vbBinaryCompare performs a case-sensitive comparison, so letter case must agree. For instance, searching “Invoice” inside “invoice paid” succeeds with text comparison but fails with binary comparison.

It is best to provide the comparison mode explicitly when the result matters. If you omit it, the behavior depends on the module’s Option Compare setting; a module with no such statement uses binary comparison. A module can declare Option Compare Text at the top, but that affects comparison behavior in that module more broadly. Passing a mode directly to InStr makes the intention visible at the search itself.

Use vbTextCompare when capitalization should not affect a match.
Use vbBinaryCompare when exact letter case is meaningful.
Do not rely on the default if different modules or maintainers might use different comparison settings.

Text comparison follows VBA’s text-comparison rules and the system’s language settings; it is not a general-purpose linguistic search engine. For ordinary English words and labels, it is usually the practical choice. If matching has legal, scientific, or other exact-text consequences, test the specific characters and comparison behavior your workbook needs.

Make a reusable Boolean function

If several procedures need the same test, put it in a helper function rather than repeating the comparison expression. This version returns True or False and lets the caller choose whether case matters. It treats an empty search string as a match, in line with how InStr handles an empty substring when a valid start position is supplied.

Public Function ContainsText(ByVal textToSearch As String, _
                             ByVal textToFind As String, _
                             Optional ByVal caseSensitive As Boolean = False) As Boolean
    Dim compareMode As VbCompareMethod

    ' Pick the comparison rule requested by the caller.
    If caseSensitive Then
        compareMode = vbBinaryCompare
    Else
        compareMode = vbTextCompare
    End If

    ContainsText = (InStr(1, textToSearch, textToFind, compareMode) > 0)
End Function

Place a worksheet-callable function in a standard VBA module: in the Visual Basic Editor, choose Insert > Module, then paste the function there. If the text to search is in cell A2, the worksheet formula =ContainsText(A2,"urgent") returns TRUE or FALSE. The function defaults to ignoring case. To request a case-sensitive check in VBA, call it with True as its third argument.

This is a VBA user-defined function, not a built-in worksheet function. The workbook must be saved in a macro-enabled format such as .xlsm, and macros must be allowed for the function to run. Excel for the web does not run VBA macros or user-defined VBA functions; open the workbook in desktop Excel. Excel for Mac supports VBA, although available features and automation interfaces can differ from Windows.

Search text stored in worksheet cells

For a one-off check in a macro, read the cell’s value and pass it to InStr. This example checks column A from row 2 to the last nonempty cell and writes “Match” or “No match” in column B. It assumes the cells in column A contain ordinary text or values that can be converted to text.

Sub MarkRowsContainingKeyword()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim searchText As String
    Dim cellText As String

    Set ws = ActiveSheet
    searchText = "urgent"
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowNumber = 2 To lastRow
        cellText = CStr(ws.Cells(rowNumber, "A").Value2)

        ' Write a result based on a case-insensitive search.
        If InStr(1, cellText, searchText, vbTextCompare) > 0 Then
            ws.Cells(rowNumber, "B").Value = "Match"
        Else
            ws.Cells(rowNumber, "B").Value = "No match"
        End If
    Next rowNumber
End Sub

Before running a macro that writes to a worksheet, save a copy of the workbook. Macro changes cannot be reversed with Ctrl+Z. On Windows, open the VBA editor with Alt+F11; on a Mac, the shortcut is commonly Option+F11, sometimes with Fn as well depending on the keyboard. You can also use the Developer tab to access Visual Basic and run macros. The Developer tab may need to be enabled in Excel’s ribbon settings.

For production code, account for cells containing Excel error values such as #N/A: converting an error value with CStr can raise a type mismatch. Check IsError before converting, and decide whether to skip the row, write an error label, or handle it another way. Also decide whether formulas returning an empty string should count as empty; Value2 reads the evaluated value, not the cell’s displayed formatting.

Get the match position, not just True or False

Because InStr returns a position, the same call can answer where a match begins. In the first example it returns 19, not merely True. This is useful when code needs to extract text around a marker, distinguish multiple occurrences, or report a more informative message. The returned position is based on the string supplied to VBA, with the first character numbered 1.

The first argument can also start a search later in the string. For example, after finding a first occurrence at position 5, searching again from position 6 can find a later occurrence. Keep track of the positions and ensure the next starting point advances; repeatedly searching from the same position can return the same match again. If you only need to know whether at least one occurrence exists, the simple greater-than-zero test is clearer and avoids unnecessary position-handling logic.

Common mistakes and edge cases

Testing for a specific position: A match can occur anywhere, so test whether the result is greater than zero rather than whether it equals 1.
Forgetting the case rule: If you omit the comparison argument, a search may be case-sensitive. State vbTextCompare or vbBinaryCompare explicitly.
Confusing contains with whole-word matching: Searching for “art” also finds the letters in “party.” InStr checks for a sequence of characters; it does not enforce word boundaries.
Passing an empty search string: An empty substring can be considered found at the specified starting position. If an empty input should mean “no search,” check Len(textToFind) = 0 first and return False or take another defined action.
Passing Null or an error value: A function declared with String arguments expects usable strings. A worksheet cell may contain an error, and a Variant may contain Null. Validate those inputs before converting or searching.
Searching formatted appearance: VBA usually searches the value you read, not the exact way Excel displays it. A date or number’s displayed text may differ from its underlying value; format or convert it deliberately if the display representation is what should be searched.

When to use alternatives

For a VBA string search, InStr is usually the simplest option. The Like operator is an alternative when a pattern is needed, such as a wildcard pattern. Its pattern rules differ from a literal substring search, and its case behavior is affected by the module’s comparison setting. Use it when pattern matching is actually required, not just to check whether a fixed word appears.

Excel’s worksheet functions SEARCH and FIND can also locate text in a cell: SEARCH is case-insensitive, while FIND is case-sensitive. From VBA, they can be called through Excel’s worksheet-function interface, but error results need handling when there is no match. For straightforward VBA logic, InStr expresses the check directly and returns zero for no match. Regular expressions are another option for complex patterns, but add setup and compatibility considerations; they are unnecessary for a literal phrase.

If a worksheet user only needs a visible result and no VBA-specific behavior, a worksheet formula may be simpler than a macro. If the task involves looping through many rows, applying custom rules, or updating cells, VBA can be appropriate. Consider workbook security and portability before choosing: a macro-enabled workbook requires users to open it in desktop Excel with macros enabled, while ordinary formulas do not require VBA.

Frequently Asked Questions

How do I check if a cell contains a word in VBA?
Read the cell’s value and test InStr(1, CStr(cell.Value2), "word", vbTextCompare) > 0. Check for cell errors before converting the value if the range may contain errors.

Is InStr case-sensitive in VBA?
It depends on the comparison argument or the module’s Option Compare setting. Pass vbTextCompare for a case-insensitive search or vbBinaryCompare for a case-sensitive one.

What does InStr return when there is no match?
It returns 0. A positive number is the starting character position of a match, so the usual contains test is whether the result is greater than zero.

Does InStr find only whole words?
No. It finds the requested sequence anywhere in the text. Searching for “cat” can match “catalog.” Whole-word checks need additional boundary logic, such as checking the characters before and after the match.

Can I use a VBA substring function in an Excel cell?
Yes. Put a Public function in a standard module and call it as a user-defined worksheet function. The workbook needs macros enabled, and the function will not calculate in Excel for the web.

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.