excelavon

Excel VBA Find a Substring in a String

VBA & macros · Updated November 3, 2026

Excel VBA Find a Substring in a String

In Excel VBA, use InStr to find a substring inside a string: it returns the position of the first match, or 0 if there is no match. For example, InStr(1, text, part, vbTextCompare) > 0 tests whether the text contains the part without distinguishing uppercase and lowercase letters.

The quickest way to use InStr

InStr is VBA’s standard tool for locating text within other text. Give it a starting position, the full string to search, the substring to look for, and optionally a comparison mode. The starting position is 1 for the first character.

Dim text As String
Dim part As String
Dim position As Long

text = "Order delayed"
part = "delayed"
position = InStr(1, text, part, vbTextCompare)

If position > 0 Then
    Debug.Print "Found at character " & position
Else
    Debug.Print "Not found"
End If

This example reports that the substring starts at character 7. If the search string is absent, position is 0. That makes a Boolean test straightforward: compare the result with zero. Use InStr(...) > 0 when you need True or False, or keep the numeric position when you also need to extract text or report where the match occurred.

What InStr returns and how its arguments work

The full syntax is InStr([start], string1, string2, [compare]). Here, string1 is the text being searched and string2 is the text you want to find. The optional start tells VBA where to begin. The optional compare controls whether the match is case-sensitive.

  • start: a 1-based character position. Use 1 to search from the beginning.
  • string1: the complete string to inspect.
  • string2: the substring being searched for.
  • compare: a comparison constant such as vbBinaryCompare or vbTextCompare.

For example, in the string “blueberry,” searching for “berry” returns 5, because the b in “berry” is the fifth character. A missing substring returns 0, not -1 and not an error. The result is a position in the original string, so it can be passed to functions such as Mid$ when you want to work with the matching text.

There is also a shorter call form when you omit the starting position: InStr(string1, string2, [compare]). In practice, supplying 1 explicitly is often clearer, particularly when code is read by someone learning VBA. Do not pass 0 as the start position; valid search positions begin at 1.

Choose whether the search is case-sensitive

To ignore case, pass vbTextCompare. For example, that mode treats “Invoice” and “invoice” as a match. To require the same character casing, use vbBinaryCompare. If you leave out the comparison argument, VBA follows the module’s Option Compare setting; when no setting is declared, the default is binary comparison.

Dim phrase As String
Dim foundPosition As Long

phrase = "Excel VBA"
foundPosition = InStr(1, phrase, "vba", vbTextCompare)

If foundPosition > 0 Then
    Debug.Print "Match found"
End If

The example finds “vba” even though the source has “VBA.” If you change the comparison constant to vbBinaryCompare, it does not find that lowercase search text in the uppercase source. Making the mode explicit avoids a result changing unexpectedly when code is moved to a module with a different Option Compare declaration.

Text comparison follows VBA’s text-comparison rules and can be affected by locale. For ordinary English words, the distinction is usually simple: text comparison ignores letter case, while binary comparison requires the same case. If a search must follow a specialized rule for accents, symbols, or a particular language, test representative strings in the locale where the workbook will run.

Make a reusable substring test

If the same test is needed in several procedures, place it in a standard VBA module as a function. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in the function. On a Mac, open the Visual Basic Editor from the Developer tab or Tools > Macro > Visual Basic Editor; keyboard shortcuts can differ by keyboard and macOS settings.

Option Explicit

Public Function ContainsSubstring(ByVal fullText As String, _
                                  ByVal fragment As String, _
                                  Optional ByVal matchCase As Boolean = False) As Boolean
    Dim compareMode As VbCompareMethod

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

    ContainsSubstring = (InStr(1, fullText, fragment, compareMode) > 0)
End Function

With the function in a standard module, a worksheet can call it like another user-defined function. With the sample text in A2 and the search term “late” typed as the second argument, enter =ContainsSubstring(A2,"late") in B2; if A2 contains “Arriving late,” the result is TRUE. This worksheet function uses case-insensitive matching by default. To require matching case, supply TRUE as its third argument.

This function accepts strings. A cell containing an Excel error value, such as #N/A, cannot be safely converted to the declared String argument in every calling situation. Check for errors before calling it if your source range may contain errors. Also note that a VBA user-defined function requires macros to be enabled and the workbook saved in a macro-enabled format such as .xlsm. Excel for the web can display a workbook containing VBA, but it cannot run or edit VBA macros.

Search a range of worksheet cells

For a one-off search across cells, a macro can loop through a range and take an action for each match. This example highlights matching cells in A2:A100, using a case-insensitive search for “urgent.” It skips cells containing Excel errors instead of attempting to convert those values to text.

Option Explicit

Sub HighlightUrgentCells()
    Dim searchRange As Range
    Dim cell As Range
    Dim searchText As String

    ' Set the cells and substring to search.
    Set searchRange = ActiveSheet.Range("A2:A100")
    searchText = "urgent"

    For Each cell In searchRange.Cells
        If Not IsError(cell.Value2) Then
            If InStr(1, CStr(cell.Value2), searchText, vbTextCompare) > 0 Then
                cell.Interior.Color = RGB(255, 255, 0)
            End If
        End If
    Next cell
End Sub

Change the range and search text to suit the sheet. The code checks the cell’s underlying value, not necessarily the exact characters shown by a custom number format. For example, a date or number may be stored as a numeric value and displayed in a formatted way. If the visible display text is what matters, investigate the cell’s .Text property, but remember that displayed text can depend on column width and formatting.

Before running a macro that changes cells, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z on Windows; on Mac, Command+Z likewise does not reverse a macro’s changes. The example also replaces the fill color of every matched cell, so use a copy if existing formatting matters.

Find later matches or search backward

InStr can continue a search after an earlier match. If a match begins at position position, set the next start to position + Len(fragment) to look after that entire occurrence. To allow overlapping matches, advance by 1 instead. For example, in “aaaa,” searching for “aa” with a one-character advance finds matches beginning at positions 1, 2, and 3; advancing by the substring length finds positions 1 and 3.

To locate the last occurrence rather than the first, use InStrRev. It searches from the end toward the beginning, while its return value is still a position counted from the left side of the string. For example, InStrRev("red, blue, red", "red", -1, vbTextCompare) returns 12, the starting position of the final “red.” This is useful for finding the final slash in a path or the last separator in a label. Check for a zero result before using the returned position to extract text.

Common problems and edge cases

  • Confusing a position with a Boolean: InStr returns a number. Test whether it is greater than 0 if you only need to know whether a match exists.
  • Searching the wrong direction: the first string argument after the start is the full text; the next is the substring. Reversing them changes the meaning of the search.
  • Assuming a whole-word match: searching for “art” also finds it inside “article” or “cart.” InStr looks for characters in sequence; it does not identify word boundaries.
  • Unexpected empty search terms: an empty substring is treated as a match at the specified starting position. If an empty input should mean “no search,” check it before calling InStr.
  • Using an invalid start: a start position of 0 is not a valid 1-based search position. A start beyond the end of the string produces no match.
  • Passing cell errors or unexpected values: error values are not ordinary strings. Check with IsError before converting cell contents in a macro.

A deliberate empty-term check can make a function’s behavior clearer. Decide what the application should do—return False, report invalid input, or treat an empty term as a match—and encode that decision before the search. There is no universally correct choice: a filter interface may want to reject an empty term, while a general-purpose search routine may preserve VBA’s built-in behavior.

When to use a different method

Use InStr when VBA code needs the position of text or a simple contains/not-contains test. It is more direct than looping through each character yourself. If you need patterns such as “three digits followed by two letters,” a regular expression is a better fit, although it requires setting up a regular-expression object and defining a pattern. If the requirement is to match an entire cell exactly, compare the cell value directly rather than using a substring search.

For worksheet-only work, Excel’s SEARCH function finds text without case sensitivity, and FIND is case-sensitive. They return a position when found and an error when not found, unlike VBA’s InStr, which returns 0 for a missing substring. In Microsoft 365 or Excel 2021, functions such as FILTER can help return rows meeting a condition, but they solve a broader worksheet-array problem and are not replacements for every VBA search. Choose based on whether the task belongs in a cell formula or a macro.

Frequently Asked Questions

Does InStr return 0 or -1 when text is not found?

It returns 0 when the substring is not found. A positive result is the 1-based position where the match begins, so a condition such as InStr(1, text, part, vbTextCompare) > 0 is a reliable contains test.

How do I make an InStr search case-insensitive?

Pass vbTextCompare as the comparison argument, for example InStr(1, fullText, fragment, vbTextCompare). Including the argument explicitly makes the intended case behavior clear regardless of the module’s Option Compare setting.

Can InStr find a whole word only?

No. It matches a sequence of characters anywhere in the string, including inside a longer word. For whole-word matching, check the characters before and after the match against your chosen word-boundary rules, or use a regular expression when the boundary requirements are more involved.

Why does my VBA function not work in Excel for the web?

Excel for the web does not run VBA macros or user-defined VBA functions. Open the macro-enabled workbook in the desktop Excel application to run the code. A worksheet formula based on a built-in Excel function may be a better option when browser-based use is required.

Can I use InStr to find the last occurrence?

Use InStrRev to search backward for the final occurrence. It still reports the matching character’s position counted from the start of the string, which makes the result usable with VBA’s normal text-extraction functions.

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.