VBA Find in a String: InStr, Examples, and Options

To find text inside a VBA string, use the InStr function. It returns the position of the first match, or 0 if there is no match; for example, InStr(1, text, searchTerm, vbTextCompare) searches without distinguishing uppercase and lowercase letters.

What InStr Does

InStr searches one string for another. Its result is a position counted from 1, not from 0, so a match at the first character returns 1. A result of 0 means VBA did not find the requested text. That makes the function useful in conditions, where you can test whether a match exists before taking an action.

The function searches for a sequence of characters, not a whole word. If you search for cat, it can match that sequence inside catalog or scatter. If you need a whole-word match, you must add boundary checks or use a more specialized search method.

InStr Syntax and Comparison Rules

The general syntax is InStr([start], string1, string2, [compare]). The square brackets indicate optional arguments; do not type the brackets in your VBA code.

start is the position where the search begins. It is optional and defaults to 1.
string1 is the text to search within.
string2 is the text to look for.
compare determines whether the comparison is case-sensitive or case-insensitive.

For a clear, explicit case-insensitive search, use vbTextCompare. For a case-sensitive, binary comparison, use vbBinaryCompare. If you omit the comparison argument, VBA follows the module’s Option Compare setting. When no such statement appears, the default is binary comparison. An explicit comparison argument is often easier for another reader to understand than relying on a module-level setting.

If you specify a comparison argument, include a start position as well. In other words, write InStr(1, text, term, vbTextCompare), rather than leaving out the first argument. In VBA, the start argument is required when the compare argument is supplied.

A Basic Search Example

This procedure looks for the word brown in a sentence and prints the position. Because the search uses vbTextCompare, it would also match BROWN or Brown.

Sub FindTextExample()
    Dim text As String
    Dim searchTerm As String
    Dim position As Long

    ' Search the sentence without distinguishing case.
    text = "The quick brown fox"
    searchTerm = "brown"
    position = InStr(1, text, searchTerm, vbTextCompare)

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

The result is position 11: the first character of brown is the eleventh character in the sentence. You can also use the returned position to extract text with functions such as Mid, or to decide whether to update a value. A search that returns 0 should be handled explicitly; using that result as though it were a valid character position can produce incorrect logic.

Choosing Case-Sensitive or Case-Insensitive Search

Use vbTextCompare when capitalization should not affect the result. It is a common choice for searches of names, notes, or descriptions where users may type the same word in different cases. For example, searching for urgent with this option can find URGENT.

Use vbBinaryCompare when capitalization matters, such as when comparing case-sensitive identifiers. With a binary comparison, Cat and cat are different strings. Text comparison follows language-specific text comparison rules, so it is best not to assume that every accented or locale-specific character behaves like a simple English letter. If a project depends on exact character handling, test with representative text from the languages and data it will process.

You can set a default for a module by placing Option Compare Text or Option Compare Binary at the top of that module, before procedures. This affects string comparisons in the module when they do not specify a comparison mode. An explicit argument in a particular InStr call makes that call’s intended behavior visible and avoids accidental changes if the module setting is altered later.

Starting at a Position and Finding More Matches

The optional start argument lets you skip earlier text. For example, InStr(5, text, term, vbTextCompare) begins at character 5. This is useful when a string may contain the search term more than once. To find successive, non-overlapping occurrences, call InStr again starting just after the previous match.

Sub FindEveryOccurrence()
    Dim sample As String
    Dim needle As String
    Dim foundAt As Long
    Dim nextStart As Long

    ' Find successive, non-overlapping matches.
    sample = "cat scatter cat"
    needle = "cat"

    If Len(needle) = 0 Then Exit Sub
    nextStart = 1

    Do
        foundAt = InStr(nextStart, sample, needle, vbTextCompare)
        If foundAt = 0 Then Exit Do
        Debug.Print foundAt
        nextStart = foundAt + Len(needle)
    Loop
End Sub

This example prints 1 and 13. It advances by the length of the search term, so overlapping matches are skipped. If your task requires overlapping matches, advance by 1 instead. The empty-term check matters: searching for an empty string can return the current start position, and advancing by its length of zero would otherwise leave the loop at the same position.

Searching Text in Worksheet Cells

InStr searches a string value; it does not search a worksheet range by itself. A macro can loop through cells, convert each usable value to text, and call InStr. The example below checks a named worksheet range and prints the addresses of cells containing urgent. Change the worksheet name and range to suit your workbook.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even though this particular example only prints results to the Immediate window. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Option+F11, which may require the Fn key depending on keyboard settings. Run macros only in workbooks and files you trust.

Sub SearchCellsForText()
    Dim ws As Worksheet
    Dim cell As Range

    ' Set the sheet and inspect each cell in the range.
    Set ws = ThisWorkbook.Worksheets("Data")

    For Each cell In ws.Range("A2:A100")
        If Not IsError(cell.Value2) Then
            If InStr(1, CStr(cell.Value2), "urgent", vbTextCompare) > 0 Then
                Debug.Print cell.Address(False, False), cell.Value2
            End If
        End If
    Next cell
End Sub

The IsError check prevents an Excel error value, such as #N/A, from being passed to CStr. The conversion lets the search inspect numbers and other ordinary cell values as text too. If you want to mark, edit, or delete matching cells, add that action deliberately and test it on a copy first. To inspect the printed results, open the Immediate window in the Visual Basic Editor, commonly with Ctrl+G on Windows.

Finding a Whole Word Instead of a Substring

A frequent surprise is that InStr reports partial matches. Searching for art can find the letters in partial, even if you meant the standalone word. The function has no built-in whole-word option. You need to define what counts as a word boundary for your data, then verify the characters before and after each match.

For simple, consistently formatted text, boundaries might be spaces, commas, or the beginning and end of the string. Real text can also use periods, parentheses, hyphens, apostrophes, or characters from other writing systems. Decide whether underscores and digits are part of a word in your particular task; there is no single boundary rule that is right for every dataset. A check that only pads the text with spaces, for example, will miss a word followed by a comma unless punctuation is handled too.

If you need flexible pattern matching, a regular expression can express word boundaries and alternatives, but it adds setup and compatibility considerations. A plain InStr search is usually simpler for a fixed phrase or substring. For whole-word requirements, test examples that should match and near-matches that should not before using the result in a larger process.

InStr Compared with InStrRev and Range.Find

InStrRev searches a string from right to left and returns the position of the last occurrence. It is useful when you need the final separator in a path or a final delimiter in a line of text. It still returns a character position, and its comparison behavior can also be specified. Use InStr when the first occurrence is wanted and InStrRev when the last one is wanted.

Do not confuse these string functions with Excel’s worksheet search feature. In VBA, Range.Find searches cells and returns a Range object for a matching cell; InStr searches characters within a string and returns a number. A worksheet search may be more appropriate when you need to locate a cell across a range, while InStr is the direct choice for checking a cell’s text after you have read it.

Another common mix-up is Excel’s Find dialog. It searches worksheet content, whereas VBA’s InStr is a function you call from code. Choose based on what you want back: a character position in text, a matching cell, or a visual search result.

Common Problems and Useful Checks

Getting 0: The term was not found under the comparison rules you chose. Check spelling, spaces, punctuation, and whether case matters.
Getting an unexpected position: Positions start at 1, and they refer to the string passed as string1, not a worksheet’s row or column number.
Matching part of a word: This is normal substring behavior. Add boundary checks if whole-word matching is required.
Searching a cell that may contain an error: Check with IsError before converting its value to a string.
Using an invalid start position: Keep the start at 1 or greater, and ensure it is within the string length for the search you intend. A search that begins beyond the available text cannot find a match.
Forgetting an empty search term: Decide how your code should handle it. In loops, guard against an empty term so the search position can keep moving.

When debugging, print both the input string and the returned position with Debug.Print. This can reveal leading or trailing spaces that are difficult to see in a worksheet. If whitespace should be ignored, consider whether trimming the text is appropriate; trimming changes the string being searched, so use it only when that matches the data rules.

Frequently Asked Questions

What does InStr return when text is not found?
It returns 0 when the search string does not occur in the text. Test for a result greater than 0 before treating it as a character position.

Is InStr case-sensitive in VBA?
It depends on the comparison setting. Use vbBinaryCompare for a case-sensitive comparison and vbTextCompare for a case-insensitive one. If omitted, the module’s Option Compare setting applies; the default is binary.

How do I find the last occurrence in a string?
Use InStrRev rather than InStr. It searches from the right and returns the position of the last occurrence.

Can InStr search an Excel column directly?
No. InStr searches a string. To examine a column, loop through its cells and pass each cell’s value to InStr, or use Range.Find when you want to locate matching cells.

Does InStr find whole words only?
No. It finds character sequences, including sequences embedded inside longer words. Add boundary checks or use a pattern-based approach if whole-word matching is essential.

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.