Excel VBA Find Text in a String: InStr Explained

In Excel VBA, use the InStr function to find one piece of text inside another: it returns the position where the match begins, or 0 if it is not found. For a case-insensitive search, pass vbTextCompare; for a case-sensitive search, use vbBinaryCompare.

How InStr Finds Text

InStr searches a string from left to right and reports the position of the first match. Positions start at 1, not 0. For example, if InStr finds “fox” at the fourth character of a longer string, it returns 4. A result of 0 means there is no match.

The common form is InStr(start, string1, string2, compare). start is the character position at which to begin searching, string1 is the text to search, string2 is the text to look for, and compare determines how characters are compared. The start position is optional in some forms, but using all four arguments makes the search behavior explicit.

For instance, searching “The quick brown fox” for “brown” returns 11. Searching the same sentence for “wolf” returns 0. The returned value is a position, not a Boolean value, so test whether it is greater than zero to determine whether the text exists.

Basic VBA Example

The following macro checks whether a phrase appears in a string, without treating uppercase and lowercase letters as different. Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and put the code in that module. On Mac, use Option+F11 or open the editor from Tools > Macro > Visual Basic Editor; keyboard behavior can depend on the Mac’s function-key settings.

Sub CheckForText()
    Dim sourceText As String
    Dim searchText As String
    Dim foundAt As Long

    sourceText = "The quick brown fox"
    searchText = "BROWN"

    ' vbTextCompare ignores differences in letter case.
    foundAt = InStr(1, sourceText, searchText, vbTextCompare)

    If foundAt > 0 Then
        MsgBox "Found at character " & foundAt
    Else
        MsgBox "Text was not found"
    End If
End Sub

Here, the message reports character 11 because the search ignores case and “BROWN” matches “brown.” To use a different example, set sourceText to a cell’s value, such as CStr(Range("A1").Value). Converting a cell value to a string is useful when the cell might contain a number or a date, though the resulting text representation can depend on how the value is formatted or converted.

Choose Case-Sensitive or Case-Insensitive Search

The comparison argument controls whether letter case matters. With vbBinaryCompare, “Cat” and “cat” are different strings. This is the usual default in VBA modules, so a search that omits the compare argument is generally case-sensitive. With vbTextCompare, the search treats letter case as equivalent, so “Cat,” “CAT,” and “cat” match.

Use vbBinaryCompare when exact capitalization matters, such as matching a case-sensitive code or identifier.
Use vbTextCompare when people may type the same word with different capitalization.
Specify the comparison argument in the call when you want the search rule to be clear regardless of module settings.

A module can also set its default comparison behavior with an Option Compare statement at the top, before procedures. Option Compare Text makes text comparisons in that module case-insensitive by default, while Option Compare Binary selects binary comparison. An explicit compare argument supplied to InStr is clearer for an individual search and avoids relying on a setting elsewhere in the module. Text comparison follows the system’s regional rules, so unusual accented or locale-specific characters may not behave like a simple English alphabet comparison.

Use the Returned Position Safely

Because a match starts at position 1, use a Long variable for the result and compare it with 0. Do not use If foundAt Then when you want the code to be easy to read: an explicit test, If foundAt > 0 Then, makes the meaning plain. Also avoid treating the returned position as the number of matches. InStr returns only the first matching position.

The starting-position argument lets you search later in the same string. For example, use a start value of 5 to ignore the first four characters. If the match begins before that point, it will not be returned. A start value beyond the length of the string produces no useful match and normally results in 0. When using a variable as the start value, check that it is a positive position appropriate to the text being searched.

If you need the text after a match, the returned position can be combined with Mid. With a known separator, first check that InStr returned a positive position, then extract the characters following it. This check matters: using a zero result as if it were a real position can extract the wrong part of the string or produce confusing output.

Search Cell Values and Partial Text

To search a worksheet cell, pass its value to InStr. For example, a macro can read Range("A1").Value, convert it to text, and search for a phrase. If the cell is empty, the result is 0. If the cell contains an Excel error value, converting or processing it as ordinary text needs extra handling; check for an error with IsError before conversion.

InStr looks for a substring, not a complete word. Searching for “art” will also match “party” or “cart.” That behavior is correct for a literal substring search, but it can be a surprise when checking categories or keywords. For whole-word matching, define what counts as a word boundary first: spaces alone are not enough if commas, hyphens, parentheses, or other punctuation can surround the word. A regular-expression search or a carefully designed tokenizing routine is more suitable when word boundaries must be enforced.

Likewise, InStr treats the search term as literal text. It does not interpret wildcard characters such as * and ? as patterns. If the search value contains an asterisk, VBA looks for an actual asterisk. This distinction separates InStr from Excel’s Find dialog and from pattern-matching approaches.

Find Multiple Matches in One String

To locate every occurrence, repeat the search and move the starting position beyond the previous match. The next example finds all occurrences of “ana” in a sample string. The matches overlap only if the next search starts one character after the previous match’s start; advancing by the search term’s length skips overlapping matches. Decide which behavior fits the task.

Sub FindAllOccurrences()
    Dim sourceText As String
    Dim searchText As String
    Dim foundAt As Long
    Dim nextStart As Long

    sourceText = "banana bandana"
    searchText = "ana"
    nextStart = 1

    Do
        foundAt = InStr(nextStart, sourceText, searchText, vbTextCompare)
        If foundAt = 0 Then Exit Do

        Debug.Print foundAt
        ' Advance one character to allow overlapping matches.
        nextStart = foundAt + 1
    Loop
End Sub

In this example, the Immediate window prints 2, 4, and 12. Open it in the Visual Basic Editor with Ctrl+G on Windows; on Mac, use the editor’s View > Immediate Window command. Advancing by Len(searchText) instead of 1 would skip overlaps. For example, in “aaaa,” searching for “aa” with a one-character advance finds positions 1, 2, and 3; advancing by two finds positions 1 and 3.

Find the Last Match with InStrRev

When you need the final occurrence rather than every occurrence, VBA provides InStrRev. It searches from the end of a string toward the beginning and returns the position of the last match, still counting positions from the start of the string. This is handy for locating the final backslash in a file path or the final period in a name. It is not the same as reversing the characters or returning a position counted from the end.

For example, using InStrRev("C:\Reports\June\summary.xlsx", "\") returns the position of the last backslash. A search for the extension’s period can similarly identify where the file extension begins. As with InStr, check the result before extracting text, because a return value of 0 means the requested character or text was absent.

InStr Compared with Excel Find

Use InStr when VBA already has a string and needs to check or locate a substring. It returns a numeric position and is well suited to conditional logic, parsing, and loops. Excel’s worksheet Find method is intended to locate a value in a range of cells; it returns a Range object for a matching cell rather than a character position within a string. The Find dialog is useful for interactive searches across a sheet or workbook.

These tools have different options and results. The worksheet Find method supports options such as matching the whole cell or searching within formulas, while InStr only compares the string arguments you supply. Find settings can persist between calls and interact with settings used in Excel’s Find dialog, so code that uses Range.Find should specify the relevant arguments rather than relying on previous dialog settings. For searching a phrase inside one cell’s text, InStr is usually the simpler choice.

Common Problems and Practical Tips

Getting 0 unexpectedly: Check for extra spaces, punctuation, line breaks, or a different spelling. A case-sensitive comparison may also be the reason the text does not match.
Getting a position instead of True or False: That is normal. Test whether the result is greater than 0 to create a yes-or-no condition.
Matching part of a word: Remember that “art” matches inside “party.” Use a whole-word method if partial matches are not acceptable.
Searching many cells: Repeatedly reading cells from the worksheet can be slow on large ranges. For substantial jobs, consider loading the range into a VBA array, then searching the array values.
Using wildcards: InStr searches literal text. Choose another method if you need wildcard or regular-expression pattern matching.

For large or important data-cleaning tasks, test the code on a small sample first and decide how to handle empty cells, errors, and non-text values. If the macro changes worksheet data, keep a backup because macro actions cannot be reversed with Excel’s Undo command. A search-only macro that reports results is easier to verify than one that immediately replaces or deletes values.

Frequently Asked Questions

Does InStr return 0 when text is not found?
Yes. A positive number is the position of the first match, and 0 means there is no match. Positions count from 1.

How do I make InStr ignore capitalization?
Pass vbTextCompare as the fourth argument, as in InStr(1, sourceText, searchText, vbTextCompare). This makes the comparison case-insensitive.

How do I check whether a string contains text in VBA?
Assign the result of InStr to a Long variable and test whether it is greater than 0. That condition is true when the substring exists.

Can InStr find more than one occurrence?
One call returns only the first match. Call it repeatedly with a start position after the previous result to find additional occurrences, taking care to choose whether overlapping matches should count.

What is the difference between InStr and InStrRev?
InStr finds the first match from the left. InStrRev finds the last match by searching from the right, but reports the position using the string’s normal left-to-right numbering.

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.