Excel VBA Find Position in a String with InStr Examples

In Excel VBA, use InStr to find the position of one string inside another: InStr(1, textValue, searchText, vbTextCompare) returns a 1-based character position, or 0 when no match exists. Use vbBinaryCompare instead when capitalization must match exactly.

What InStr Returns

InStr is VBA’s standard function for locating a substring. A substring is a run of characters contained within a longer string: for example, 2048 is within Invoice 2048 paid. The result is a number, not the matching text itself. In that example, the position of 2048 is 9 because the initial I is position 1 and the space is position 8.

Positions start at 1, which is important when you use the result to split or inspect a string. A result of 0 means the search text was not found. Do not treat 0 as the first character; VBA string positions do not start at zero.

The four-argument form makes the choices explicit:

InStr(start, string1, string2, compare)

start is the character position where searching begins.
string1 is the text to search in.
string2 is the text to find.
compare controls whether the comparison is case-sensitive.

The shorter form, InStr(string1, string2), is also valid. It begins at position 1 and uses the module’s comparison setting. For code that other people may maintain, explicitly supplying the start and comparison mode usually makes the intended behavior clearer.

A Basic VBA Example

This example searches a sentence for an invoice number and reports its location. Save a copy of the workbook before running a macro: VBA macros cannot be undone with Ctrl+Z in Windows or Command+Z on Mac, even when a particular macro only displays a message.

Sub FindInvoiceNumber()
    Dim textValue As String
    Dim searchText As String
    Dim position As Long

    ' Search this example sentence for the number.
    textValue = "Invoice 2048 paid"
    searchText = "2048"
    position = InStr(1, textValue, searchText, vbTextCompare)

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

The macro displays “Found at character 9.” The test position > 0 is the important check: it distinguishes a match from the 0 returned when the search fails. In a larger procedure, you can replace the message box with code that writes the position to a cell, extracts text, or makes a decision.

To try the example, open the Visual Basic Editor from the Developer tab, choose Insert > Module, and paste the procedure into the module. Run it from the editor or from Excel’s macro controls. If the workbook contains valuable data, keep the original or a separate backup before running any code that changes cells.

Case-Sensitive and Case-Insensitive Searches

VBA offers two common comparison constants. vbBinaryCompare compares character values and is case-sensitive; vbTextCompare performs a case-insensitive text comparison according to the applicable text-comparison rules. For example, a binary search for excel will not match Excel, while a text comparison will.

Choose the mode based on what counts as a match in your data. Case-insensitive search is often useful for names or labels entered inconsistently. A case-sensitive comparison can matter for identifiers where uppercase and lowercase are meaningful. The comparison mode does not make the search a wildcard or pattern search: InStr looks for the literal sequence of characters you supply.

You can set the comparison mode directly in the call:

Sub CompareCaseModes()
    Dim sourceText As String
    Dim position As Long

    ' Compare the same word using both modes.
    sourceText = "Excel VBA"
    position = InStr(1, sourceText, "excel", vbBinaryCompare)
    MsgBox "Binary comparison result: " & position

    position = InStr(1, sourceText, "excel", vbTextCompare)
    MsgBox "Text comparison result: " & position
End Sub

The first result is 0 because the capitalization differs; the second is 1. A module can also use an Option Compare statement to set a default comparison for functions that omit the compare argument. An explicit argument is less dependent on module settings, so it is a dependable choice for reusable code.

Finding a Match After a Certain Position

The first argument lets you continue a search from a later point in the source string. This is useful when a value can appear more than once, such as repeated separators, repeated words, or a list of codes embedded in a line. Each call returns the next match at or after the supplied starting position.

For instance, to find a second occurrence after the first matched word, start after the end of the first match. The following example searches without regard to case:

Sub FindSecondOccurrence()
    Dim sourceText As String
    Dim searchText As String
    Dim firstPosition As Long
    Dim secondPosition As Long

    ' Find the second occurrence of the word.
    sourceText = "red, blue, red"
    searchText = "red"
    firstPosition = InStr(1, sourceText, searchText, vbTextCompare)

    If firstPosition > 0 Then
        secondPosition = InStr(firstPosition + Len(searchText), _
                               sourceText, searchText, vbTextCompare)
    End If

    If secondPosition > 0 Then
        MsgBox "Second occurrence starts at " & secondPosition
    Else
        MsgBox "There is no second occurrence."
    End If
End Sub

Here the first occurrence starts at 1 and the second starts at 12. Len(searchText) advances the next search to the character after the first complete match, so this finds non-overlapping occurrences. If overlapping matches should count—for example, finding aa twice in aaa—advance by 1 instead of by the search term’s length.

Searching from the Right with InStrRev

When you need the last occurrence rather than the first, use InStrRev. It searches backward through the string and returns the position of the last match. This is handy for locating the final period in a filename, the last slash in a path, or the final delimiter in a line of text.

For a filename such as report.final.xlsx, the last period identifies where the extension begins. This example shows the position of that final period:

Sub FindLastPeriod()
    Dim fileName As String
    Dim position As Long

    ' Locate the final period in the filename.
    fileName = "report.final.xlsx"
    position = InStrRev(fileName, ".", -1, vbBinaryCompare)

    If position > 0 Then
        MsgBox "The final period is at character " & position
    Else
        MsgBox "No period was found."
    End If
End Sub

The result is 14. InStrRev is not simply a different spelling of InStr: its arguments are arranged differently. Its first argument is the text being searched, followed by the text to find; the optional start position and comparison argument come after those. Check that order when adapting a call from InStr.

Using InStr with Worksheet Cell Values

VBA can search text stored in a cell by passing the cell’s value as the source string. For example, if A2 contains a customer note and you want to know whether it contains urgent, assign the cell value to a string variable and use InStr. A position greater than 0 means the text occurs somewhere in that cell’s value.

For a one-off check, you can use the result directly in an If statement. For a list of rows, loop through the relevant cells and test each result. Avoid assuming every cell contains ordinary text: blank cells, numbers, and error values can need separate handling. A cell containing an Excel error value may not convert cleanly to a VBA string, so check for errors with IsError before using its value as text.

Remember that VBA’s InStr is not a worksheet formula. In a worksheet, Excel provides functions such as FIND and SEARCH for locating text. Those functions return a position when they find a match and an error when they do not; FIND is case-sensitive, while SEARCH is not. They also support wildcard behavior that ordinary InStr does not. Use the worksheet functions in cell formulas, and use InStr in VBA procedures or functions.

Common Problems and How to Avoid Them

Assuming the first character is position 0: VBA uses 1-based positions. The first character is position 1, and a missing match is 0.
Using a result without checking it: If you calculate a position and immediately take text before or after it, a missing match can lead to invalid or unintended results. Test whether the result is greater than 0 first.
Mixing up source and search text: In InStr(start, string1, string2), string1 is the larger string being searched, and string2 is the fragment you want to locate.
Forgetting capitalization rules: Make the comparison explicit when exact case matters. Otherwise, the module’s Option Compare setting can determine the default.
Expecting wildcard matching: InStr treats characters such as * and ? literally. It does not interpret them as patterns.
Searching an error-valued cell as text: Check cell values with IsError before assigning them to string variables or passing them to string functions.

Also be cautious when using a position in another operation. If you remove a prefix or alter the text before searching again, the old position may no longer refer to the same character. Calculate positions from the current string rather than carrying a position forward after changing the string.

Choosing Between InStr, InStrRev, FIND, and SEARCH

Use InStr when VBA code needs the first match from a chosen starting point. Use InStrRev when the last match is what matters. Both return 0 for no match, which makes their results straightforward to test in VBA. The two functions are suitable for literal substring searches, with a comparison mode available for case handling.

Use worksheet FIND or SEARCH when the result belongs in a cell formula or when you want worksheet wildcard behavior. Use FIND for case-sensitive formula searches and SEARCH for case-insensitive searches. Their not-found behavior differs from VBA’s 0 result: worksheet functions return an error, so a formula may need error handling if a missing match is expected.

Another practical distinction is where the result is used. A VBA macro can inspect a value, branch on the position, and process many cells in one procedure. A worksheet formula recalculates as its referenced cells change and is visible directly in the grid. Pick the approach that fits the task rather than moving data into VBA just to perform a simple worksheet calculation.

Frequently Asked Questions

Does InStr return the character position or the number of characters?
It returns the 1-based position where the matching text begins. It does not return the match length; use Len if you also need the number of characters in the search text.

What does InStr return when it cannot find a match?
It returns 0. Test for a result greater than 0 before using it as a position or passing it to code that expects a valid match.

Is InStr case-sensitive?
It depends on the comparison mode. Specify vbBinaryCompare for case-sensitive matching or vbTextCompare for case-insensitive matching. If omitted, the module’s comparison setting applies.

Can InStr find the second or third occurrence?
Yes. Call InStr again with a start position after the previous match. Advance by the search text’s length for non-overlapping matches, or by 1 if overlapping matches should count.

How do I find the last occurrence of text in VBA?
Use InStrRev, which searches backward and returns the position of the last match. Its argument order differs from InStr, so supply the source text and search text in the order shown in its syntax.

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.