Excel VBA: Find a Character Position in a String with InStr

In Excel VBA, use InStr to find where a character or text fragment first appears in a string: it returns a 1-based position, or 0 if there is no match. For example, InStr(1, "Invoice-42", "-") returns 8; the optional comparison argument lets you choose case-sensitive or case-insensitive matching.

How InStr finds a position

The basic 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 to search, and string2 is the character or substring to find. The returned number counts from 1, not 0, so the first character in a string is at position 1.

The return value is a Long. It is the position of the first match at or after the specified starting position. If VBA cannot find the search text, it returns 0. That makes it straightforward to test for a match before using the position:

Dim position As Long

' Look for the first hyphen in the text.
position = InStr(1, "Invoice-42", "-", vbBinaryCompare)

If position > 0 Then
    Debug.Print "Found at position " & position
Else
    Debug.Print "No match"
End If

This example prints position 8. A key distinction is that InStr searches for a string, not only a single character: the second argument can be a longer fragment such as "2026" or ".xlsx". If your procedure is specifically intended to accept exactly one character, validate the input rather than assuming it is one.

A simple reusable VBA function

For code that needs the same operation in several places, put the search in a function. In the Visual Basic Editor, insert a standard module and add this function. It returns 0 if the target is not exactly one character or if the character is absent; otherwise it returns the first position.

Public Function FindCharPosition(ByVal sourceText As String, _
                                 ByVal targetChar As String) As Long
    ' Return 0 for an invalid target or a character that is not found.
    If Len(targetChar) <> 1 Then Exit Function

    ' Use binary comparison so the match is case-sensitive.
    FindCharPosition = InStr(1, sourceText, targetChar, vbBinaryCompare)
End Function

Because the function is Public and sits in a standard module, it can also be called as a worksheet user-defined function. With INV-42 in cell A2, enter =FindCharPosition(A2,"-") in another cell; the result is 4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain the VBA, and enable macros only when you trust the workbook and its source. The function expects text inputs; a cell containing an Excel error value may cause a VBA type mismatch.

Search from a particular position

The first argument is useful when the first match is not the one you need. It tells VBA where to begin looking, and that position is included in the search. For example, in the text "Banana", the letter “a” appears at positions 2, 4, and 6. Starting at position 3 skips the first “a” and returns 4.

Dim position As Long

' Start at character 3 and find the next lowercase a.
position = InStr(3, "Banana", "a", vbBinaryCompare)
Debug.Print position  ' Prints 4

You can use this behavior to find repeated delimiters: after each match, start the next search one position beyond the previous result. Check for 0 before adding 1, or you could continue searching from position 1 after a failed search. Also ensure that the starting position is sensible for the string you are searching; a position beyond the available text will not produce a match.

Choose case-sensitive or case-insensitive matching

The optional fourth argument controls how VBA compares the strings. vbBinaryCompare compares character values and distinguishes uppercase from lowercase, so searching for "a" does not match "A". vbTextCompare performs a text comparison and generally treats those letters as equal. For predictable code, specify the comparison mode explicitly rather than relying on a module setting.

Dim caseSensitivePosition As Long
Dim caseInsensitivePosition As Long

' The first search distinguishes uppercase and lowercase.
caseSensitivePosition = InStr(1, "Excel VBA", "v", vbBinaryCompare)

' The second search ignores that case distinction.
caseInsensitivePosition = InStr(1, "Excel VBA", "v", vbTextCompare)

In this example, the binary search returns 0 because the text contains uppercase “V,” not lowercase “v.” The text comparison returns 7. If you omit the comparison argument, VBA uses the module’s Option Compare setting; absent an explicit text-comparison setting, the normal default is binary comparison. Locale can affect text comparisons, so use an explicit mode when the result matters to data processing.

Search a worksheet range with a macro

To find a character in many cells, loop through the cells and write each result to a neighboring column. The example below searches column A for the first period and records its position in column B. It assumes the active worksheet has text values in A2:A100 or a similar continuous list; the code finds the last used row in column A.

Sub FindPeriodsInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim sourceText As String

    ' Work on the currently active worksheet.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Store the first period's position, or 0 if none is found.
    For rowNum = 2 To lastRow
        sourceText = CStr(ws.Cells(rowNum, "A").Value)
        ws.Cells(rowNum, "B").Value = _
            InStr(1, sourceText, ".", vbBinaryCompare)
    Next rowNum
End Sub

Before running a macro that writes to a workbook, save a copy: VBA macros cannot be undone with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11; on a Mac, use Option+F11 (some keyboards also require Fn), or open it from the Developer tab. This macro writes only to column B, but confirm that column is available before running it. It treats an empty cell as an empty string and returns 0 for it.

Find the last occurrence instead

InStr returns the first match from the start position onward. To locate the final occurrence in a string, use InStrRev, which searches from the end toward the beginning and also returns a 1-based position. For example, it is often useful for finding the final backslash in a file path or the final period in a filename.

Dim lastPeriod As Long

' Find the final period in a filename.
lastPeriod = InStrRev("report.final.xlsx", ".", -1, vbBinaryCompare)
Debug.Print lastPeriod  ' Prints 14

Use InStrRev only when you need the last match; it is not a substitute for repeatedly calling InStr when you need every occurrence. For file extensions, the last period is a useful starting point, but it does not by itself validate that a filename has a meaningful extension.

Common mistakes and edge cases

Expecting a zero-based result: VBA string positions start at 1. A returned 0 means no match, not that the match is the first character.
Using the arguments in the wrong order: The text to search comes before the search text. InStr(1, sourceText, targetChar) is the usual form, not the reverse.
Forgetting case: A binary comparison distinguishes uppercase and lowercase. Pass vbTextCompare if that distinction should not matter.
Confusing a character with a substring: InStr accepts either. If a function name promises a single-character search, check that the target has length 1.
Using the result without checking it: If the result is 0, it is not a valid character position. Test it before extracting text or calculating an offset.

An empty search string is a special case: VBA treats it as a match at the starting position when the arguments are otherwise valid. If an empty target should count as “not found” in your application, reject it explicitly. Also remember that an Excel worksheet error is not ordinary text; converting such a cell to a VBA string can raise an error, so validate input cells if the range may contain errors.

InStr versus worksheet FIND and SEARCH

VBA’s InStr runs in VBA code and returns a number you can use in a procedure. Excel’s worksheet functions FIND and SEARCH solve a related problem in a cell formula: FIND is case-sensitive, while SEARCH is not. They report an error when they cannot find the requested text, whereas InStr returns 0. Choose the VBA function for a macro or user-defined function, and a worksheet function when you want the result to update as worksheet data changes without running a macro.

Frequently Asked Questions

Does InStr return the first or last match?
InStr returns the first match at or after its starting position. Use InStrRev to search from the end and return the last match.

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

How do I make InStr ignore uppercase and lowercase?
Pass vbTextCompare as the fourth argument, as in InStr(1, sourceText, targetText, vbTextCompare). Use vbBinaryCompare for a case-sensitive comparison.

Can InStr find a word or a group of characters?
Yes. Its search argument can be a single character or a longer substring. The result is the position where the first matching substring begins.

Is the position returned by InStr zero-based?
No. It is 1-based: the first character is position 1. A return value of 0 indicates that no match was found.

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.