VBA Find a Character in a String

In VBA, use the InStr function to find a character in a string: it returns the character’s position, or 0 if the character is not present. For example, InStr(1, "hello", "e", vbBinaryCompare) returns 2.

How InStr Finds a Character

InStr searches one string for another and reports the position where the first match begins. Although you can search for a word or a longer sequence, a single-character search works the same way: pass the character as the search string. The result is a number, so you can store it in a Long variable or use it in a condition.

VBA positions are one-based. The first character is position 1, not position 0. If the search string is not found, InStr returns 0. That makes a simple test possible:

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

    ' Search for an at sign using a binary comparison.
    sampleText = "ada@example.com"
    position = InStr(1, sampleText, "@", vbBinaryCompare)

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

In this example, the message reports position 4. The check for a result greater than zero is important: treating 0 as a valid position can lead to incorrect branches or calculations.

InStr Syntax and Arguments

The general form 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 sets the position from which the search begins, while compare determines how text is compared.

start: an optional positive position in the string. Use 1 to search from the beginning.
string1: the source text.
string2: the character or text to locate.
compare: an optional comparison mode, such as vbBinaryCompare or vbTextCompare.

For clarity, explicitly supplying the start and comparison mode is often a good habit. If you omit the comparison argument, the result may depend on the module’s Option Compare setting. When you provide a comparison argument, use a valid start argument first, as in InStr(1, sourceText, "x", vbTextCompare).

Choose Case-Sensitive or Case-Insensitive Search

For letters, decide whether uppercase and lowercase should count as the same character. With vbBinaryCompare, case is significant: searching for lowercase "a" will not match uppercase "A". With vbTextCompare, VBA performs a text comparison, so those letters are treated as equivalent in ordinary text searches.

For example, with sourceText containing "Banana", InStr(1, sourceText, "a", vbBinaryCompare) returns 2, while a search for "A" with that same mode returns 0. If the search for "A" uses vbTextCompare, it returns 2. The first result is the earliest match, not the count of all matches.

Option Compare Text can set a text-comparison default for a VBA module, and Option Compare Binary sets a binary-comparison default. These statements belong at the top of a module, before procedures. For code whose behavior should be obvious regardless of module settings, pass vbBinaryCompare or vbTextCompare directly to InStr. Text comparisons can also be affected by locale rules, so use binary comparison when you need a straightforward, case-sensitive match.

Find the First Match and Test for Presence

If all you need to know is whether a character occurs anywhere, you do not need to store or display its exact position. Store the result and test whether it is greater than zero. This is useful for basic validation, such as checking whether a value contains a hyphen or a slash before processing it. Remember that finding a character does not prove the rest of the text is valid; it only confirms that this particular search string occurs.

You can also use the returned position to split or inspect text. Suppose a string contains a dash separating a code from a description. If InStr returns a position greater than zero, the characters before that position are the prefix. The position is the index of the dash itself, so the number of characters before it is one less than the returned position. Functions such as Left$ and Mid$ can then extract parts of the string, provided you check for the not-found result before using the position.

For a search that may fail, handle 0 before calling another string function with the result. For example, trying to use a zero position as though it were the location of a separator can produce the wrong substring or an error in later logic. A clear If position > 0 Then branch makes the intended behavior explicit.

Find Every Occurrence

InStr returns only the first match from the specified starting point. To find every occurrence, repeat the search, moving the start position forward after each match. The following procedure prints the positions of every case-insensitive occurrence of the letter a in its sample string to the Immediate window in the VBA editor:

Sub FindEveryA()
    Dim sampleText As String
    Dim target As String
    Dim position As Long
    Dim nextStart As Long

    ' Continue searching after each match.
    sampleText = "Banana"
    target = "a"
    nextStart = 1

    Do
        position = InStr(nextStart, sampleText, target, vbTextCompare)
        If position = 0 Then Exit Do
        Debug.Print position
        nextStart = position + Len(target)
    Loop
End Sub

For this example, the output is 2, 4, and 6. Advancing by Len(target) moves past the match. Since the target here is one character long, that advances by one position. If you search for a longer sequence and want to include overlapping matches, advance by 1 instead. For instance, searching for "aa" in "aaa" can find matches at positions 1 and 2 if the next search starts one character after the previous match. Make sure a repeated-search loop always advances; otherwise, it can find the same match forever.

Search from the End with InStrRev

If you need the last occurrence rather than the first, use InStrRev. It searches backward through a string and returns the position of the last match, still using one-based positions. For example, searching for a backslash in a Windows-style file path can locate the final separator, which is often useful when extracting the file name. A result of 0 still means no match was found.

The argument order differs from InStr: InStrRev(stringcheck, stringmatch, [start], [compare]). The string to search comes first, followed by the match. That difference is easy to overlook when switching between the functions. Use InStr when you need the first match or want to search forward from a chosen position; use InStrRev when the last match is what matters.

Common Problems and Misconceptions

Expecting a Boolean result: InStr returns a position, not True or False. Compare the result with 0 to test for presence.
Using zero-based indexing: VBA string positions begin at 1. A match in the first character returns 1.
Ignoring case: The comparison mode determines whether uppercase and lowercase letters match. Specify it when the distinction matters.
Searching for an empty string: An empty search string is not a useful character search and can produce a position that surprises readers. Validate that the target contains a character before searching when it comes from user input.
Confusing a character with a byte: InStr returns a character position in the VBA string; it is not a byte offset into a file or a memory buffer.
Assuming it searches a worksheet range: InStr compares strings in VBA. It does not locate a character across cells unless your code reads each cell and searches its value.

VBA strings can contain characters beyond basic English letters, and comparison behavior can depend on the chosen mode and locale. If your task involves unusual Unicode text, test with representative data and be precise about whether you need character matching, case folding, or byte-level processing. For routine searches in ordinary cell text, an explicit comparison mode and a check for 0 are usually sufficient.

Using InStr with an Excel Cell

A macro can read a cell’s value into a string and then pass that string to InStr. The short example below assumes cell A1 contains text and reports whether it includes a hyphen. It uses Value2 to read the cell value and CStr to convert it to a string.

Sub CheckCellForHyphen()
    Dim cellText As String
    Dim position As Long

    ' Read the text in A1 and look for a hyphen.
    cellText = CStr(ActiveSheet.Range("A1").Value2)
    position = InStr(1, cellText, "-", vbBinaryCompare)

    If position > 0 Then
        MsgBox "A hyphen is present at position " & position
    Else
        MsgBox "No hyphen was found"
    End If
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a larger procedure might change cells as well as inspect them. This example only reads a value and displays a message, but keeping a backup is a sensible precaution when adapting code. If a cell can contain an Excel error value rather than text, handle that case before converting it with CStr.

VBA InStr Versus Excel Find and Worksheet Functions

Use InStr inside VBA code. If you want to search for a character directly in a worksheet formula, Excel’s SEARCH and FIND functions are alternatives: SEARCH is case-insensitive, while FIND is case-sensitive. Unlike InStr, these worksheet functions return an error if the text is absent rather than returning 0.

With the text ada@example.com in cell A2, enter =SEARCH("@",A2) in another cell; it returns 4. In the same layout, =FIND("@",A2) also returns 4 because the at sign has no uppercase or lowercase form. These are worksheet formulas, not VBA statements, so do not paste them into a VBA procedure as though they were calls to InStr.

Excel’s Find command is another option when you want to locate matching content interactively in a sheet. It is useful for navigating cells, while InStr is better suited to logic inside a macro. Choose according to where the search happens: a VBA string, a worksheet formula, or a user-operated workbook search.

Frequently Asked Questions

What does InStr return if the character is missing?
InStr returns 0 when it does not find the requested character or text. Test for a result greater than 0 before using it as a string position.

Is VBA string searching case-sensitive?
It depends on the comparison mode. Use vbBinaryCompare for case-sensitive matching and vbTextCompare for case-insensitive text matching. Passing the mode explicitly avoids relying on a module default.

How do I find the last occurrence of a character?
Use InStrRev, with the source string first and the character to find second. It returns the position of the last match, or 0 when there is no match.

How can I count a character in a string?
Repeat InStr, starting each new search after the previous match, and increment a counter for each result. Advance the starting position every time so the loop cannot revisit the same occurrence.

Can I use InStr to search a whole Excel column?
Not by itself: InStr takes strings, not a range of cells. A macro can loop through the cells, convert each relevant value to text, and call InStr for each one; worksheet formulas or Find may be simpler for a one-off search.

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.