VBA Find Position of a Substring in a String

In VBA, use InStr to find a substring’s position within a string: it returns the position of the first match, or 0 if there is no match. For example, InStr(1, "Blue sky", "sky", vbBinaryCompare) returns 6.

The Basic InStr Syntax

InStr is VBA’s standard function for searching one string inside another. Its full syntax is InStr([start], string1, string2, [compare]). Here, string1 is the text to search, and string2 is the text to find. The optional start argument sets the position where the search begins; the optional compare argument controls whether matching is case-sensitive.

For clear, predictable code, supply all four arguments, including the starting position and comparison mode. This example searches a variable and stores the result as a number:

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

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

    ' Search from the first character, matching letter case.
    position = InStr(1, sourceText, searchText, vbBinaryCompare)

    MsgBox position  ' Displays 11.
End Sub

Before running a macro that changes workbook data, save a copy of the workbook: macros cannot be undone with Ctrl+Z. This sample only displays a result, but the precaution matters when you adapt it to edit cells.

How InStr Positions Work

VBA string positions start at 1, not 0. In the example above, the first character, “T,” is at position 1, and “brown” begins at position 11. The returned number identifies the first character of the match, not the character after it and not the number of characters searched.

A missing match returns 0, which makes a simple test possible. For example, if the text is “The quick brown fox” and the search term is “wolf,” the result is 0. Check for that result before using the position to extract or replace text; otherwise, code that assumes a match exists may produce the wrong result.

The start argument is also one-based. Setting it to 5 means the search begins at the fifth character of the source string, but the result is still the match’s position in the original string. It is not a position counted anew from the starting point. If the start value is greater than the source string’s length, no match is found and the result is 0.

Choose Case-Sensitive or Case-Insensitive Matching

The comparison argument determines how VBA treats uppercase and lowercase letters. Use vbBinaryCompare for a case-sensitive match: “Cat” and “cat” are different. Use vbTextCompare for a case-insensitive text match, so either capitalization can match.

Sub CompareSearchModes()
    Dim position As Long

    ' Case-sensitive search: returns 0 because the cases differ.
    position = InStr(1, "Excel VBA", "vba", vbBinaryCompare)
    Debug.Print position

    ' Case-insensitive search: returns 7.
    position = InStr(1, "Excel VBA", "vba", vbTextCompare)
    Debug.Print position
End Sub

If you omit the compare argument, the module’s Option Compare setting controls the comparison. With no Option Compare statement, VBA uses binary comparison. A module can instead start with Option Compare Text to make its default comparison textual and case-insensitive. An explicit comparison argument is usually easier to understand when reading a particular search, and it avoids relying on a module-level setting.

Text comparison follows the language rules of the system, so accented letters and other locale-sensitive characters may not behave like a simple English-only comparison. If exact character matching matters, use vbBinaryCompare explicitly.

Find a Match and Handle No Match

A practical search usually needs to distinguish a successful match from a missing one. Test whether the returned position is greater than zero before using it. The following procedure reports either outcome:

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

    sourceText = "Invoice 4827 paid"
    position = InStr(1, sourceText, "4827", vbTextCompare)

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

For this sample, the message says the match starts at position 9. That result can be used in additional logic, such as extracting the text after an identifier or checking whether a cell contains a marker. Keep the original source string available if you will use the position later: the number is meaningful only relative to the exact string searched.

Search from a Later Position or Find the Last Match

To find a later occurrence, set start to a position after the first match. For example, search for the next comma by starting one character after the comma already found. Always check that the earlier search returned a positive position before adding 1; otherwise, a missing first match can lead to a meaningless start value.

Sub FindSecondComma()
    Dim sourceText As String
    Dim firstPosition As Long
    Dim secondPosition As Long

    sourceText = "red,green,blue"
    firstPosition = InStr(1, sourceText, ",", vbBinaryCompare)

    If firstPosition > 0 Then
        secondPosition = InStr(firstPosition + 1, sourceText, ",", vbBinaryCompare)
        Debug.Print secondPosition  ' Displays 10.
    End If
End Sub

For repeated searches, put this pattern in a loop and stop when InStr returns 0. To search from the right for the last occurrence, use InStrRev. For instance, InStrRev("reports\2026\summary.xlsx", "\") returns 13, the position of the last backslash. It searches right-to-left, but its returned position is still counted from the start of the string.

Use the Position to Extract Text

Once InStr finds a delimiter, combine its result with VBA’s string functions. Left returns characters from the beginning, while Mid returns characters starting at a specified position. For example, if text contains first.last@example.com, finding the at-sign lets you separate the name before it from the domain after it.

Sub SplitAtAtSign()
    Dim text As String
    Dim atPosition As Long
    Dim userPart As String
    Dim domainPart As String

    text = "first.last@example.com"
    atPosition = InStr(1, text, "@", vbBinaryCompare)

    If atPosition > 0 Then
        userPart = Left(text, atPosition - 1)
        domainPart = Mid(text, atPosition + 1)
        Debug.Print userPart   ' first.last
        Debug.Print domainPart ' example.com
    End If
End Sub

The check is important because a missing delimiter would make the calculated boundaries incorrect. This example illustrates string parsing, not full email-address validation; finding an at-sign alone does not establish that an address is valid.

Common Mistakes and Edge Cases

Expecting a zero-based result: VBA’s position is one-based. The first character is at 1, and a failed search returns 0.
Forgetting case behavior: An omitted comparison argument uses the module’s default. Specify vbBinaryCompare or vbTextCompare when the intended behavior should be obvious.
Using the wrong start value: The optional starting position changes where VBA begins looking, not how the returned position is counted.
Assuming every search succeeds: Check for a result greater than 0 before passing it to Left, Mid, or other logic that depends on a valid match.
Confusing an empty search term with a missing term: An empty string is a special case; InStr returns the start position for a zero-length search string. Validate user-provided search terms if blank input should count as an error.

Also distinguish text stored in a worksheet cell from a VBA string variable. A macro can read a cell’s value into a string and then call InStr, but the function itself does not search a worksheet range or highlight matching cells. Those tasks require additional VBA code or worksheet features.

InStr Compared with Excel FIND and SEARCH

InStr is a VBA function used in code. Excel’s worksheet functions FIND and SEARCH are entered in cells. All can return a one-based position, but their names and calling contexts differ. In a worksheet formula, FIND is case-sensitive and SEARCH is case-insensitive. In VBA, InStr handles both behaviors through its comparison argument.

For example, in a worksheet cell, =FIND("VBA",A2) searches the value in A2 with case sensitivity, while =SEARCH("VBA",A2) ignores case. In a macro, use InStr(1, cellText, "VBA", vbTextCompare) for a case-insensitive search. These worksheet formulas are not VBA statements, and InStr is not a worksheet formula you can type into a cell.

Choose based on where the search belongs: use a worksheet function when the result should update in a cell as worksheet values change; use InStr when a VBA procedure needs to make a decision, extract part of a string, or process text as it runs.

Frequently Asked Questions

What does InStr return when the substring is not found?
It returns 0. Test for a result greater than 0 before treating it as a valid character position.

Does InStr count from zero or one?
It counts from 1. The first character is position 1, and a found substring’s position refers to its first character.

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

How do I find the last occurrence of a substring?
Use InStrRev, which searches from the right. Its returned position is still measured from the beginning of the string.

Can I use InStr in an Excel cell formula?
No. InStr is a VBA function. Use worksheet functions such as FIND or SEARCH in cells, depending on whether the match should be case-sensitive.

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.