VBA Find Position of a Character in a String with InStr

Use VBA’s InStr function to find a character in a string: it returns the character’s 1-based position, or 0 if the character is not found. For example, InStr(1, "North, South", ",", vbBinaryCompare) returns 6.

How InStr Reports a Character’s Position

InStr searches one string for another and tells you where the match begins. Although this question often refers to a single character, the search text can be a word or any other sequence of characters. The returned number counts from the beginning of the string, starting at 1—not from 0 as in some programming languages.

A result of 1 means the match starts at the first character.
A result greater than 1 gives the match’s starting position.
A result of 0 means there is no match.

In the example above, “North” occupies positions 1 through 5, and the comma is at position 6. The space after the comma is position 7. This distinction matters when you use the returned position to extract text or move through a string.

InStr Syntax and Arguments

The full syntax is InStr([start], string1, string2, [compare]). The square brackets indicate optional arguments; do not type the brackets into your code. string1 is the text being searched, and string2 is the character or sequence you want to find.

start is the position where the search begins. If omitted, the search starts at position 1. A start position must be at least 1.
string1 is the string to search.
string2 is the text to locate.
compare optionally specifies how text is compared, such as case-sensitive binary comparison or case-insensitive text comparison.

Specify all four arguments when you want your code’s behavior to be explicit. For example, the following macro searches for a comma and displays its position. It reports a separate message if the comma is missing.

Sub FindCommaPosition()
    Dim textValue As String
    Dim position As Long

    textValue = "North, South"
    position = InStr(1, textValue, ",", vbBinaryCompare)

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

Choose the Right Comparison Mode

For punctuation such as commas or hyphens, vbBinaryCompare is a clear choice. It compares character values and distinguishes uppercase from lowercase letters. If the character or text you are searching for is a letter, the comparison option determines whether capitalization matters.

Use vbTextCompare when you want a case-insensitive text search. For instance, searching for “a” in “Maple” with this option can match the uppercase “A.” With vbBinaryCompare, that lowercase search does not match the uppercase character. The choice affects letters, not the meaning of the returned position.

If you omit compare, VBA uses the module’s Option Compare setting. A module that has Option Compare Text at the top uses text comparison by default; Option Compare Binary selects binary comparison. If no setting is specified, binary comparison is the default. Explicitly passing vbBinaryCompare or vbTextCompare makes the intended behavior easier to see and maintain.

Examples for Common Character Searches

For a simple search, save the result in a Long variable and test it before using it. For example, with a string of an email address, locating the at sign can help determine whether the string contains that character. Finding it does not, by itself, validate the address; it only answers whether the requested text occurs.

This macro searches for the first at sign in a sample string. The at sign is the sixth character, so the message displays position 6.

Sub FindAtSign()
    Dim emailText As String
    Dim position As Long

    emailText = "sam@example.com"
    position = InStr(1, emailText, "@", vbBinaryCompare)

    If position > 0 Then
        MsgBox "The at sign is at position " & position
    Else
        MsgBox "The at sign was not found."
    End If
End Sub

To search from a later position, provide a different start. In “red, blue, green,” searching for a comma from position 6 skips the first comma and finds the second one. The result is still measured from the start of the original string; it is not a count of characters from the search’s starting point.

Sub FindSecondComma()
    Dim textValue As String
    Dim position As Long

    textValue = "red, blue, green"
    position = InStr(6, textValue, ",", vbBinaryCompare)

    MsgBox "The second comma is at position " & position
End Sub

Find More Than One Occurrence

InStr returns the first match at or after its starting position. To locate every occurrence, search repeatedly and update the start position after each match. This example finds each comma in a string. It advances by one character after a match, which also allows overlapping matches when the search text contains more than one character.

Sub FindEveryComma()
    Dim textValue As String
    Dim position As Long
    Dim startAt As Long

    textValue = "red, blue, green"
    startAt = 1

    Do
        position = InStr(startAt, textValue, ",", vbBinaryCompare)
        If position = 0 Then Exit Do

        Debug.Print position
        startAt = position + 1
    Loop
End Sub

Open the Immediate window in the Visual Basic Editor to see the printed positions; the Windows shortcut is Ctrl+G. This sample prints 4 and 10. If you search for a longer sequence and do not want overlapping matches, advance by Len(searchText) instead of 1. Always move the starting point forward: otherwise, a loop can find the same match repeatedly.

Related Functions and When to Use Them

Use InStr when you need the first match, a match from a particular starting point, or a result that can drive later VBA logic. For example, the returned position can be passed to Mid to extract text around a delimiter, or checked before you split a string. Remember that positions are 1-based in both InStr and VBA’s string-extraction functions.

InStrRev is useful when you need to search from the end of a string, such as finding the last backslash in a file path. It returns the position of the match counted from the beginning of the original string—not the number of characters counted backward. For a filename, that position can help identify where the final folder separator occurs.

For more advanced pattern matching, VBA also has the Like operator, but it answers whether a string matches a pattern; it does not directly return the position of the match. A regular-expression object can locate patterns too, but it involves additional setup. For a single character or ordinary substring, InStr is usually the simpler and more direct tool.

Common Problems and How to Avoid Them

Confusing not found with position 1: A zero result means no match. Check for a result greater than zero before passing the position to other operations.
Assuming the result starts at zero: VBA string positions start at 1. The first character is position 1.
Forgetting case sensitivity: Choose vbBinaryCompare or vbTextCompare deliberately when letters are involved.
Searching from an invalid position: Start at 1 or later. If a calculated starting position could be zero, correct it before calling InStr.
Using an empty search string unintentionally: An empty string is not a useful character to locate and can produce a result that surprises readers. Check that the search text has a nonzero length when it comes from a variable.

Another easy mistake is treating the result as a character count after the match. It is the starting position of the match. If you find the two-character sequence “ab” at position 4, the returned position is 4, not 5 or 6.

Using InStr in an Excel Macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then insert a standard module using Insert > Module. On a Mac, Option+F11 opens the editor on many keyboards; you may need Fn+Option+F11 depending on the keyboard and system settings. You can also open the editor from Excel’s Developer tab by choosing Visual Basic. If the Developer tab is hidden, enable it in Excel Options on Windows or Excel’s ribbon settings on Mac.

Put the search in a macro or a VBA procedure that already processes the relevant text. For example, a macro can read a cell’s value into a string variable, call InStr, and then decide what to do based on the result. Be mindful that cell values may not be text; convert or check values as appropriate for the workbook’s data. The function searches the string you pass to it, not a worksheet range directly.

Before running a macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is the practical way to protect against unintended changes. A macro that only displays or prints a position does not need to modify cells, but broader code may do so.

Frequently Asked Questions

What VBA function finds a character in a string?
Use InStr. It searches for a character or longer sequence and returns the position of the first match, or 0 when no match is found.

Does InStr return a zero-based position?
No. VBA positions are 1-based, so the first character is at position 1. A return value of 0 has a different meaning: the search text was not found.

How do I find the last occurrence of a character?
Use InStrRev to search from the end. Its return value is still the match’s position counted from the beginning of the string.

How can I make the search ignore capitalization?
Pass vbTextCompare as the comparison argument, as in InStr(1, textValue, "a", vbTextCompare). This lets a search for lowercase “a” match uppercase “A.”

Can InStr find a word as well as one character?
Yes. The search argument can be any substring, such as a word, a space, or a multi-character delimiter. The returned position is where that substring begins.

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.