VBA Find in Text: Use InStr or Search Worksheet Cells

To find one piece of text inside another in Excel VBA, use InStr; it returns the position of the match, or 0 if there is no match. To search worksheet cells instead, use the range’s Find method. Choose based on whether your input is a VBA string or a cell range.

What “find in text” means in VBA

VBA offers different search tools for different kinds of content. If a macro already has text in a variable and needs to check whether a word or phrase occurs inside it, InStr is usually the most direct choice. It can also locate the match’s character position, which lets you extract or examine the surrounding text.

If the text is stored in Excel cells and you want to identify a matching cell, use Range.Find. It works much like Excel’s Find command, but lets code specify the range and search options. The distinction matters: InStr does not search a worksheet range by itself, and Range.Find returns a cell reference rather than a character position in a string.

For example, checking whether a description contains “urgent” is a string-search task. Locating every cell in column A that contains “urgent” is a worksheet-search task. A macro may use both: read a cell’s value, check it with InStr, and then take an action on that cell.

Use InStr to find a word or phrase

The basic syntax is InStr([start], string1, string2, [compare]). string1 is the text to search, and string2 is the text to look for. The optional starting position tells VBA where to begin. The optional comparison setting controls whether the match is case-sensitive.

InStr(1, text, needle, vbBinaryCompare) uses a binary, case-sensitive comparison.
InStr(1, text, needle, vbTextCompare) uses a text comparison, so it normally treats uppercase and lowercase letters as equivalent.
A result greater than 0 is the position of the first match; a result of 0 means no match was found.

In the following example, the search is case-insensitive. With the stated sample values, “URGENT” is found at position 14 because the search begins at the “u” in “urgent.”

Sub CheckForWord()
    Dim textToSearch As String
    Dim searchText As String
    Dim matchPosition As Long

    ' Example text and search term.
    textToSearch = "Please review this urgent request."
    searchText = "URGENT"

    ' Search without distinguishing uppercase from lowercase.
    matchPosition = InStr(1, textToSearch, searchText, vbTextCompare)

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

VBA character positions start at 1, not 0. If the text is “Excel VBA” and the search term is “VBA,” the returned position is 7. That position can be used with functions such as Mid to extract text, but check the result first: when InStr returns 0, there is no valid match position to use.

Choose case sensitivity and a starting position

Use vbBinaryCompare when capitalization must match exactly, such as when a macro is checking case-sensitive codes. Use vbTextCompare for ordinary words when “Invoice,” “INVOICE,” and “invoice” should count as the same search term. If you omit the comparison argument, the result can depend on the module’s Option Compare setting. Supplying the comparison explicitly makes the intent easier to understand and maintain.

The optional start argument is useful when searching repeatedly through one string. Start the next search just after the previous match to find later occurrences. A loop should stop when the result is 0; otherwise, it can run indefinitely. Here is a small example that reports the first occurrence after the beginning of the text, starting its search at character 5:

Sub FindAfterBeginning()
    Dim sampleText As String
    Dim position As Long

    sampleText = "red, blue, red"
    position = InStr(5, sampleText, "red", vbTextCompare)

    If position > 0 Then
        MsgBox "The next red begins at character " & position
    Else
        MsgBox "No later match found."
    End If
End Sub

For that sample, the result is 11. A search for an empty string is a special case: InStr returns the starting position. If the search term comes from a cell or user input, decide how your macro should treat a blank term rather than accidentally treating it as a successful match.

Search worksheet cells with Range.Find

Use Range.Find when the target is a cell or group of cells. The method returns a Range object for the first matching cell, or Nothing if there is no match. In the example below, the search covers A1:A100 on the active sheet, looks for the term anywhere within a cell’s value, and ignores letter case.

Sub FindTextInCells()
    Dim searchArea As Range
    Dim foundCell As Range
    Dim searchText As String

    ' Save a copy of the workbook before running a macro.
    searchText = "urgent"
    Set searchArea = ActiveSheet.Range("A1:A100")

    Set foundCell = searchArea.Find(What:=searchText, _
        After:=searchArea.Cells(searchArea.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No matching cell was found."
    Else
        MsgBox "Found in " & foundCell.Address(False, False)
    End If
End Sub

With this code, LookAt:=xlPart finds the search text within a longer cell value; change it to xlWhole when the entire cell content must match. LookIn:=xlValues searches displayed cell values. Use xlFormulas when you need to search the formulas themselves rather than their calculated results. The After argument sets where the search starts; Excel then wraps around the range as it searches.

Find more than one matching cell

Find returns one match at a time. To process every matching cell, call FindNext after the first result and stop when the search comes back to the original address. Excel searches wrap around to the beginning of the range, so simply calling FindNext until it returns Nothing is not a safe loop for a range with matches.

For a multi-match loop, save the first match’s address, process the current cell, call FindNext, and continue while the returned cell exists and its address is not the saved address. Keep the search options consistent with those used in the initial Find. This approach is useful for highlighting, counting, or collecting all matching cells. If your macro changes cell contents while searching, plan that carefully: altering the search results can affect which cells remain eligible or where the search continues.

For a short fixed range, another option is to loop through its cells and use InStr on each cell’s value. That can make custom tests easier to read, but it checks cells one by one and may be slower on a very large range. Use Find for ordinary worksheet searching; use a cell loop when the matching rule needs logic that Find does not express conveniently.

Common problems and how to avoid them

Using the wrong search tool: If you need a character position inside a string, use InStr. If you need the address or value of a matching cell, use Range.Find.
Forgetting the no-match result: Test for 0 after InStr, or test whether the returned range is Nothing after Find. A missing match is not automatically an error, but code must handle it.
Leaving Find settings implicit: Excel retains several Find settings between calls and between VBA and the Find dialog. Specify options such as LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, and SearchFormat to make a macro predictable.
Searching formulas instead of results: A cell may display a word because its formula returns that word. Decide whether the search should inspect the displayed value or the formula text, then set LookIn accordingly.
Ignoring data types: A cell may contain an error value, a number, or an empty cell rather than ordinary text. If reading cell values into strings, account for errors and blanks instead of assuming every cell is a normal string.

Also remember that InStr looks for a literal sequence, not a general pattern. Searching for “cat” can match “catalog.” If whole-word matching matters, a simple substring check may be insufficient; add boundary checks or use a regular expression where the pattern is complex.

InStr, Find, Like, and regular expressions

InStr is usually the clearest tool for a literal substring in a string. Range.Find is designed for locating cells and offers worksheet-oriented options such as searching values or formulas, matching an entire cell, and moving through a range. Neither one is automatically the best for every task.

The VBA Like operator tests whether a string fits a pattern that can use wildcard characters, such as * for a sequence of characters and ? for one character. It returns a Boolean result rather than the position of a match. Its comparison behavior is affected by Option Compare, so set the module option deliberately if that matters. For richer pattern rules—such as alternatives, character groups, or repeated matches—VBA can use the Microsoft VBScript Regular Expressions library. That adds setup and pattern complexity, so it is unnecessary for a straightforward word search.

One common misconception is that Excel’s worksheet SEARCH function and VBA’s InStr are interchangeable. Both can locate text, but they are used in different contexts: SEARCH is a worksheet formula, while InStr is a VBA function. A macro can use either approach when appropriate, but for a direct VBA string check, InStr keeps the logic in VBA.

Run a search macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic, or press Alt+F11. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. In the editor, use Insert > Module, paste the code into the module, and run the procedure with F5 while the cursor is inside it, or return to Excel and run it from Developer > Macros.

In Excel for Mac, use Developer > Visual Basic if the Developer tab is available; the editor shortcut is typically Option+F11, and some keyboards require Fn as well. Macro and editor availability can depend on the workbook’s format and security settings. Save macro-enabled workbooks as .xlsm when retaining VBA code.

Save a copy of the workbook before running a macro that changes data. VBA macros cannot be undone with Ctrl+Z (or Command+Z on Mac), so a backup is the simplest way to recover from an unintended change. The examples above only display a message, but a search macro may later be extended to edit or delete matching cells; test changes on a copy first.

Frequently Asked Questions

How do I check whether a string contains text in VBA?

Use InStr(1, textToSearch, searchText, vbTextCompare) and test whether the result is greater than 0. The explicit comparison makes the check case-insensitive; use vbBinaryCompare when capitalization must match.

Does InStr return the position or the text?

It returns a Long position for the first match, counting from 1. It returns 0 when the requested text is not found. It does not return the matched substring.

How do I find text in an Excel cell with VBA?

Use Range.Find on the cells you want to search, then check whether its result is Nothing. Set options such as LookIn, LookAt, and MatchCase explicitly so the search behaves as intended.

Is InStr case-sensitive?

It depends on the comparison argument or the module’s Option Compare setting. Pass vbTextCompare for a case-insensitive text comparison or vbBinaryCompare for a case-sensitive binary comparison.

How can I find every occurrence of a word?

For repeated occurrences within one string, call InStr again from the character after the previous match and stop at 0. For multiple matching cells, use FindNext and stop when the search returns to the first matching cell’s address.

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.