Excel VBA: Find a Character’s Position in a String

Excel VBA: Find a Character's Position in a String

In Excel VBA, use InStr to find a character’s position in a string: it returns the character’s one-based position, or 0 if the character is not found. For example, InStr(1, "Excel VBA", "V", vbBinaryCompare) returns 7.

Find a character with InStr

InStr searches one string for another. The first string is the text to search, and the second is the character or substring to find. Although the question often refers to a single character, the same function works for a word or any other sequence of characters. Its result is a position counted from the beginning of the string, with the first character at position 1.

If there is no match, the result is 0. That makes it important to check for zero before using the result as a character position. For example, Mid$ can extract the character at a valid position, but position 0 does not represent a character in a VBA string.

InStr(1, "banana", "a", vbBinaryCompare) returns 2, the first occurrence of a.
InStr(1, "banana", "z", vbBinaryCompare) returns 0 because z is absent.
InStr(1, "banana", "nan", vbBinaryCompare) returns 3, showing that the search can target a substring as well as one character.

The value 1 in these examples is the starting position for the search. Starting at 1 searches the entire string from its first character.

A complete VBA example

This macro stores the text and search character in variables, calls InStr, and handles both possible outcomes. The V in Excel VBA is the seventh character: E, x, c, e, l, a space, then V.

Sub FindCharacterPosition()
    Dim textToSearch As String
    Dim characterToFind As String
    Dim position As Long

    ' Set the text and the single character to locate.
    textToSearch = "Excel VBA"
    characterToFind = "V"

    position = InStr(1, textToSearch, characterToFind, vbBinaryCompare)

    If position > 0 Then
        MsgBox "Character found at position " & position
    Else
        MsgBox "Character not found."
    End If
End Sub

To try it in Excel for Microsoft 365 on Windows, save a copy of the workbook first, then press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure by pressing F5. If prompted, save a macro-enabled workbook with the .xlsm extension. Macros cannot be undone with Ctrl+Z, so keep the original workbook or a backup before running a macro that changes data.

Excel for the web does not run VBA macros. Excel for Mac supports VBA, although the keyboard shortcut and editor menus can vary by keyboard and version; use Tools > Macro > Visual Basic Editor if the shortcut is unavailable. The example only displays a message and does not change worksheet cells.

Understand the InStr arguments

The general form is InStr([start, ]string1, string2, [compare]). The square brackets in this notation mean that an argument is optional; they are not typed into the VBA call. string1 is the text being searched, string2 is the text being sought, and start specifies the position at which the search begins.

For example, if textToSearch contains "banana", then InStr(3, textToSearch, "a", vbBinaryCompare) begins at position 3 and returns 4. It skips the a at position 2, but finds the next a at position 4. A start position greater than the string length cannot find a match. Use a positive position within the string when you intend to search it.

The optional compare argument determines how text is compared. Passing it explicitly makes the code’s intent clear. If it is omitted, the module’s Option Compare setting controls comparison; when no such statement is present, VBA uses binary comparison by default. A module can begin with Option Compare Text to set text comparison as its default, but an explicit argument is often easier to understand when reading a particular search.

Choose case-sensitive or case-insensitive matching

With vbBinaryCompare, upper- and lowercase letters are treated as different. Searching for lowercase "v" in "Excel VBA" therefore returns 0, while searching for uppercase "V" returns 7. This is the appropriate choice when capitalization must match exactly, such as when looking for a specific code or symbol.

With vbTextCompare, the search ignores case for ordinary letter comparisons. For example, InStr(1, "Excel VBA", "v", vbTextCompare) returns 7. The comparison follows VBA’s text-comparison rules and locale; it is not a general-purpose way to normalize every possible Unicode character. If a task has special linguistic or symbol-matching requirements, test the relevant text explicitly rather than assuming all visually similar characters compare alike.

Use the named constants rather than unexplained numeric values. vbBinaryCompare and vbTextCompare communicate the intended behavior, making the code less prone to confusion when it is revisited or shared.

Find a later or repeated occurrence

InStr returns only the first match at or after the supplied start position. To find every occurrence, call it repeatedly and move the next start position beyond the match just found. This example counts occurrences of a character without counting overlapping matches, which is suitable for a one-character search.

Sub CountCharacterOccurrences()
    Dim textToSearch As String
    Dim characterToFind As String
    Dim searchFrom As Long
    Dim position As Long
    Dim occurrenceCount As Long

    ' Search for lowercase a in the sample text.
    textToSearch = "banana"
    characterToFind = "a"
    searchFrom = 1

    Do
        position = InStr(searchFrom, textToSearch, characterToFind, vbBinaryCompare)
        If position = 0 Then Exit Do
        occurrenceCount = occurrenceCount + 1
        searchFrom = position + Len(characterToFind)
    Loop

    MsgBox "Occurrences found: " & occurrenceCount
End Sub

For the sample word, the macro reports 3. After it finds a at position 2, the next search starts at 3; it then finds positions 4 and 6 before returning 0. If you search for a multi-character substring and need to count overlapping matches, advance by 1 instead of by Len(characterToFind). For instance, the matches for "ana" in "banana" overlap at positions 2 and 4.

For a quick search for the last occurrence, use InStrRev. Its argument order differs from InStr: the string to check comes first, followed by the string to find. For example, InStrRev("banana", "a", -1, vbBinaryCompare) returns 6. This is useful when locating the final separator in a path or the last instance of a character. Do not swap its arguments with the InStr order.

Avoid common position and input mistakes

A position returned by InStr is not a zero-based index. In VBA string functions such as Mid$, the first character is at position 1, so a successful result can be used directly as a starting position. This differs from some programming languages and libraries that number the first character as 0.

Check the result first. A result of 0 means no match, not that the first character matched. Branch on position > 0 before extracting or replacing text at that position.
Check the search input. If the variable intended to hold a character can be empty or contain more than one character, validate it with Len. InStr accepts a substring, so an accidental multi-character value searches for that entire sequence.
Use the intended starting point. A start value greater than 1 intentionally skips earlier matches. Use 1 when you want the first occurrence in the whole string.
Choose comparison behavior deliberately. A result of 0 may reflect a case mismatch, not an absent letter. Use vbTextCompare when the search should ignore case.
Distinguish spaces from empty text. A string containing a space is not empty. Searching for a space can locate separators between words, while an empty search value is not a useful substitute for a character.

When examining text imported from a file or copied from another application, also consider whether the apparent character is actually a different symbol, such as a straight apostrophe versus a typographic apostrophe. InStr finds matching text; it does not automatically replace lookalike punctuation or remove extra spaces.

InStr in VBA versus worksheet search functions

InStr is a VBA function, so use it in a macro or another VBA procedure. It is not entered as a worksheet formula. In worksheet cells, Excel provides FIND for case-sensitive text searches and SEARCH for case-insensitive searches; these worksheet functions return a position when a match is found and an error when it is not. That missing-match behavior is different from InStr, which returns 0.

Choose based on where the result is needed. Use VBA when code must make a decision, loop through text, or perform an action such as writing results to cells. Use a worksheet function when the position should appear and update directly in a cell. For a one-off check, Excel’s Find dialog is another option, but it locates matching content in the worksheet rather than returning a string position to VBA code.

Use the result to extract or update text

Once a match is found, the position can guide other string operations. For example, if position is greater than zero, Mid$(textToSearch, position, 1) returns the character at that position. For the sample text "Excel VBA" and a search for V, that expression returns "V". You can also use the position to split text around a delimiter, provided you check that the delimiter was found and decide how to handle text before and after it.

For example, if a value is "report.csv", finding the period with InStr(1, fileName, ".", vbBinaryCompare) gives 7. That position can help identify where an extension begins, but it does not by itself validate a filename or prove that the final period marks the extension. If there may be multiple periods, use InStrRev to locate the last one, then handle the no-match case separately.

When applying a search to worksheet data, be clear about whether you are examining a cell’s displayed value, its underlying value, or a string created by converting a value. Dates and numbers can have different text representations depending on formatting and conversion choices. Convert values intentionally before searching, and test the procedure on representative data before processing a large range.

Frequently Asked Questions

Does InStr return 0 or an error when a character is missing?
InStr returns 0 when it does not find the requested text. Check for 0 before using the result as a position.

Is the first character at position 0 or 1 in VBA?
For InStr and the usual VBA string operations, the first character is at position 1. A successful search for the first character therefore returns 1.

How do I make InStr ignore uppercase and lowercase?
Pass vbTextCompare as the compare argument, as in InStr(1, textToSearch, characterToFind, vbTextCompare). Use vbBinaryCompare when capitalization must match.

How can I find the last occurrence of a character?
Use InStrRev, with the text to search first and the character or substring second. It returns the last matching position, or 0 if there is no match.

Can I use InStr directly in an Excel cell?
No. InStr is a VBA function. For a cell formula, use worksheet functions such as FIND or SEARCH, depending on whether the search 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.