Excel VBA: Find a String Within a String

Use VBA’s InStr function to find one string inside another: it returns the character position of the first match, or 0 if there is no match. For example, InStr(1, "Quarterly Sales Report", "sales", vbTextCompare) returns 11 because the search ignores capitalization.

How InStr Finds a Match

InStr compares a search string with a larger string and reports where the first match begins. Its position is one-based: the first character is position 1, not position 0. This makes the result useful both for checking whether a match exists and for working out where it occurs.

For example, searching for Sales in Quarterly Sales Report gives position 11. Searching for Invoice in the same text gives 0. A test such as position > 0 therefore means “found,” while position = 0 means “not found.”

The function returns the first occurrence. If the search term appears more than once, InStr does not report every position in one call; you can search again from a later starting position to find a subsequent match.

InStr Syntax and Arguments

The VBA syntax is InStr([start, ]string1, string2, [compare]). In normal use, provide all four arguments so the starting position and case behavior are clear:

start is the character position at which the search begins. Use 1 to search from the beginning.
string1 is the longer string being searched.
string2 is the substring to look for.
compare determines how the strings are compared. Common choices are vbBinaryCompare and vbTextCompare.

The returned number is a Long. A result of 0 indicates no match. When working with values from cells, remember that an Excel cell may contain an error value or an empty cell rather than ordinary text; handle those cases before converting values to strings if they can occur in your data.

Case-Sensitive and Case-Insensitive Searches

Choose the comparison mode according to what should count as a match. With vbBinaryCompare, capitalization matters: cat and Cat are different. With vbTextCompare, VBA performs a text comparison that generally treats those forms as equivalent. The precise text comparison can depend on the system’s locale.

If the comparison argument is omitted, VBA uses the module’s Option Compare setting. In a module without an explicit setting, the default is binary comparison. A module can declare Option Compare Text at its top to make text comparison the default there, but that setting affects comparisons throughout the module. Passing vbTextCompare or vbBinaryCompare directly to InStr makes an individual search’s intent easier to see.

For predictable code, explicitly set the comparison mode when capitalization matters. Avoid changing the module-wide setting just to fix one search unless you want that behavior to apply to other comparisons in the same module as well.

Basic VBA Example: Return the Position

This short procedure searches a sample sentence without changing worksheet data. Because it uses vbTextCompare, the lowercase search word matches the capitalized word in the sentence.

Sub FindSubstringPosition()
    Dim sourceText As String
    Dim searchText As String
    Dim position As Long

    ' Search from the first character, ignoring capitalization.
    sourceText = "Quarterly Sales Report"
    searchText = "sales"
    position = InStr(1, sourceText, searchText, vbTextCompare)

    If position > 0 Then
        MsgBox "Found at character " & position
    Else
        MsgBox "No match found"
    End If
End Sub

To try it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. On a Mac, Option+F11 usually opens the editor; some keyboards require holding Fn as well. You can also open the editor from the Developer tab. If that tab is hidden, enable it in Excel’s ribbon settings.

This example only displays a message, but it is still sensible to save a copy of a workbook before running a macro. Macros can make changes that Ctrl+Z cannot undo. Excel for the web does not run VBA macros; use the desktop Excel application for this code.

Search Text Stored in Worksheet Cells

In a common layout, put the full text in A2 and the term to search for in C2. The following macro writes the matching position to B2. It assumes A2 and C2 contain ordinary text or values that can be converted to text, not Excel error values.

Sub FindTextInCells()
    Dim sourceText As String
    Dim searchText As String
    Dim position As Long

    ' Read the full text and search term from the worksheet.
    sourceText = CStr(ThisWorkbook.Worksheets("Sheet1").Range("A2").Value)
    searchText = CStr(ThisWorkbook.Worksheets("Sheet1").Range("C2").Value)
    position = InStr(1, sourceText, searchText, vbTextCompare)

    ' Write 0 when there is no match; otherwise write its position.
    ThisWorkbook.Worksheets("Sheet1").Range("B2").Value = position
End Sub

For this example, if A2 contains Quarterly Sales Report and C2 contains sales, B2 receives 11. If C2 contains invoice, B2 receives 0. Change Sheet1 to the actual worksheet name. If your cells might contain errors such as #N/A, test for those with IsError before calling CStr, because converting an error value to a string can raise a VBA error.

If you need a result for every row, place the search term in a fixed cell and loop through the source range, writing each result to a corresponding output cell. Keep the input and output ranges separate if you do not want the macro to overwrite the text being searched.

Return True or False Instead of a Position

Sometimes the exact character position is not useful; you only need a yes-or-no answer. A user-defined function can wrap InStr and be called from a worksheet formula. Add the function below to a standard VBA module. It returns TRUE when the text is present, using a case-insensitive comparison.

Public Function ContainsText(ByVal textToSearch As Variant, _
                             ByVal textToFind As Variant) As Boolean
    Dim sourceText As String
    Dim searchText As String

    ' Return False for cell errors or Null values.
    If IsError(textToSearch) Or IsError(textToFind) Then Exit Function
    If IsNull(textToSearch) Or IsNull(textToFind) Then Exit Function

    sourceText = CStr(textToSearch)
    searchText = CStr(textToFind)

    ' Treat an empty search term as not found for this function.
    If Len(searchText) = 0 Then Exit Function
    ContainsText = (InStr(1, sourceText, searchText, vbTextCompare) > 0)
End Function

With the full text in A2 and the search term in C2, enter =ContainsText(A2,C2) in a worksheet cell. This is a custom VBA function, not a built-in Excel function: the workbook must retain its VBA code, typically by being saved as an Excel Macro-Enabled Workbook (.xlsm), and macros must be permitted for the function to calculate. The function deliberately treats an empty search term as not found; adjust that rule if your task defines an empty term differently. Save a copy before running macros, since their changes cannot be undone with Ctrl+Z.

InStr Compared with FIND and SEARCH

VBA’s InStr is for code running in the Visual Basic for Applications environment. In a worksheet formula, Excel has the separate functions FIND and SEARCH. They also return a position or an error when the text is absent, but their behavior and syntax belong to worksheet formulas, not VBA statements.

Use InStr inside a VBA procedure or function. It returns 0 when there is no match and lets you choose a comparison mode.
Use worksheet FIND when a formula should search text and capitalization should matter. It returns an error if the search text is absent.
Use worksheet SEARCH for a case-insensitive formula search. It supports wildcard characters, unlike a plain InStr search.

For example, if A2 contains the text to inspect and C2 contains the term, the worksheet formula =ISNUMBER(SEARCH(C2,A2)) gives TRUE when the term is present and FALSE when it is absent. This is useful when a cell formula is sufficient and you do not need a macro. The formula uses Excel’s worksheet functions; it is not VBA syntax.

Common Problems and Useful Variations

Checking for exactly 1: InStr returns the position, so testing whether the result equals 1 only detects a match at the beginning. Test whether it is greater than 0 to detect a match anywhere.
Reversing the arguments: The longer text goes in string1 and the term being sought goes in string2. Swapping them searches in the wrong direction.
Expecting wildcards: InStr looks for a literal sequence of characters. It does not treat * or ? as wildcard patterns. Use a worksheet SEARCH formula if its wildcard behavior is what you need.
Searching after a previous match: Supply a later starting position to look for another occurrence. For example, if the first match begins at position 5 and the search term is 3 characters long, start the next search at position 8 to continue after that match.
Searching backward: VBA’s InStrRev searches from the end of a string and returns the position of the last occurrence. Choose it when the rightmost match is the one you need.

Also consider whether spaces or punctuation are part of the data. Searching for sale finds that sequence inside wholesale, because InStr checks for a substring, not a whole word. If you need whole-word matching, you must add logic to check the characters around the match or use a more specialized pattern approach.

Frequently Asked Questions

Does InStr return 0 or an error when it cannot find text?
InStr returns 0 when the search string is not found in the source string. This is why a condition such as If InStr(1, sourceText, searchText, vbTextCompare) > 0 Then is a common way to test for a match.

Is InStr case-sensitive?
It depends on the comparison mode. Use vbBinaryCompare for a case-sensitive search and vbTextCompare for a generally case-insensitive text search. If you omit the mode, the module’s Option Compare setting determines the default.

Can InStr find a word anywhere in a cell?
Yes. Pass the cell’s value as the source string and the word as the search string. The function looks for the sequence anywhere, including inside a longer word; it does not automatically enforce whole-word boundaries.

How do I find the second occurrence?
Call InStr again with a starting position after the first match. For a match beginning at position p with a search term of length n, a typical next starting position is p + n. Check that the first result is greater than 0 before calculating the next position.

Can I use VBA in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros. Open the file in the desktop version of Excel to use InStr in VBA.

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.