Excel VBA Find Character in a String: InStr Examples

In Excel VBA, use the InStr function to find a character or a longer piece of text inside a string. It returns the position of the first match, starting at 1; if there is no match, it returns 0. For example, InStr(1, textToSearch, characterToFind, vbBinaryCompare) performs a case-sensitive search.

What InStr returns

InStr searches one string for another and reports where the first occurrence begins. Its result is a position, not the character itself and not a True-or-False value. In the string blueberry, for example, the first b is at position 1, while the first e is at position 4. VBA counts from 1 rather than 0, which is important when you use the result to extract or replace text.

Although the question often says “find a character,” the search argument can be a word, code, punctuation mark, or any other substring. Searching for berry in blueberry returns 5. The function finds a sequence of characters as written; it does not interpret the search text as a pattern or regular expression.

There are three useful outcomes to account for: a positive number means a match was found, 0 means no match was found, and an error may occur if the input or arguments are invalid. Testing for a positive position is therefore the normal way to ask whether a string contains a character.

Write a basic VBA search

This small procedure searches a sample string for a hyphen and prints the result in the Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows if it is not visible. The example uses explicit variable declarations and a binary comparison so the case behavior is clear.

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

    ' Set the sample text and character.
    textToSearch = "Report-Q4"
    characterToFind = "-"

    ' Search from the first character, using a case-sensitive comparison.
    position = InStr(1, textToSearch, characterToFind, vbBinaryCompare)

    If position > 0 Then
        Debug.Print "Found at position " & position
    Else
        Debug.Print "Character not found"
    End If
End Sub

The Immediate window displays Found at position 7, because the hyphen is the seventh character in Report-Q4. Before running a macro in a workbook, save a separate copy: changes made by VBA macros generally cannot be undone with Ctrl+Z. This example only prints a message, but the precaution matters for procedures that edit cells.

On Windows, open the VBA editor with Alt+F11, or use Developer > Visual Basic. On Mac, use Developer > Visual Basic or Option+F11; some keyboards require the Fn key as well. VBA macros run in desktop Excel, not Excel for the web. The search expression itself is the same in supported desktop versions.

Understand the InStr arguments

The general form is InStr([start], string1, string2, [compare]). The square brackets mean an argument is optional in the documented syntax; they are not characters to type. string1 is the text to search, and string2 is the character or substring to find. The optional start argument tells VBA where to begin searching, while compare controls how text is compared.

start: A one-based position in the string. Use 1 to search from the beginning, or a later position to continue after an earlier match.
string1: The complete string being searched. It can be a variable, a cell value converted to text, or a literal string.
string2: The search target. A single character is simply a substring of length one.
compare: Usually vbBinaryCompare for a case-sensitive search or vbTextCompare for a case-insensitive text search.

For example, with Report-Q4 as the search string, starting at position 8 and looking for Q returns 8. Starting after position 8 cannot find that earlier Q, so the result is 0. A start value greater than the string length likewise finds no match. Use a Long variable for the result; it is an appropriate numeric type for string positions.

Search a worksheet cell

To search a cell in a macro, read its value and pass the resulting text to InStr. The following example checks cells A2:A100 on a worksheet named Data and writes Found or Not found in the adjacent column. It assumes those cells contain ordinary text or values that can be converted to text, not Excel error values such as #N/A.

Sub MarkRowsContainingHyphen()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchText As String

    ' Set the worksheet and the character to look for.
    Set ws = ThisWorkbook.Worksheets("Data")
    searchText = "-"

    ' Check each cell and write the result in the next column.
    For Each cell In ws.Range("A2:A100")
        If InStr(1, CStr(cell.Value), searchText, vbBinaryCompare) > 0 Then
            cell.Offset(0, 1).Value = "Found"
        Else
            cell.Offset(0, 1).Value = "Not found"
        End If
    Next cell
End Sub

Here CStr converts the cell value to a string before searching. Blank cells become empty strings and produce no match. If the range may contain error values, check IsError(cell.Value) before converting the value; otherwise, conversion can fail. Also change the worksheet name and range to match your workbook. This macro writes results into column B, so save a copy before running it if those cells contain data you need to keep.

Find every occurrence, not just the first

A single call to InStr returns only the first match at or after its start position. To find later occurrences, call it again with a start position after the previous match. The sample below searches for ana in banana and prints each starting position.

Sub FindAllOccurrences()
    Dim sourceText As String
    Dim needle As String
    Dim searchFrom As Long
    Dim foundAt As Long

    ' Set the text and target; reject an empty target to avoid a loop.
    sourceText = "banana"
    needle = "ana"
    searchFrom = 1

    If Len(needle) = 0 Then Exit Sub

    Do While searchFrom <= Len(sourceText)
        foundAt = InStr(searchFrom, sourceText, needle, vbBinaryCompare)
        If foundAt = 0 Then Exit Do

        Debug.Print foundAt
        searchFrom = foundAt + 1
    Loop
End Sub

This prints 2 and 4. Advancing by one permits overlapping matches: the two occurrences of ana share the middle a. If you want to count only non-overlapping matches, set the next start position to foundAt + Len(needle) instead. For a one-character target, advancing by one is the natural way to check each position in turn. Always ensure the start position advances; otherwise, a loop can repeatedly find the same match.

Choose case-sensitive or case-insensitive matching

By default, the comparison behavior can depend on the module’s Option Compare setting. If no setting is specified, VBA uses binary comparison. A binary comparison distinguishes uppercase from lowercase, so searching for a does not match A. To make the intended behavior obvious and consistent, pass a comparison constant directly to InStr.

Use vbBinaryCompare when uppercase and lowercase should count as different, such as when matching a case-sensitive identifier.
Use vbTextCompare when the search should ignore case, such as when finding a word in ordinary user-entered text.

For instance, searching Excel for e with vbBinaryCompare finds the lowercase e at position 3. Searching the same string with vbTextCompare finds the first letter at position 1, because E and e are treated as equivalent for the comparison. Text comparison follows language and locale rules, so it is not always equivalent to a simple ASCII-only case conversion.

A module can also contain Option Compare Text or Option Compare Binary at the top, before procedures. That setting affects comparisons where an explicit comparison argument is omitted. For code that needs predictable behavior, an explicit vbBinaryCompare or vbTextCompare in each relevant call is easier to read and maintain.

InStr, InStrRev, FIND, and SEARCH

Choose the tool based on where the search happens and what result you need. InStr is suited to VBA logic: it returns a numeric position you can test in an If statement, use to split a string, or pass to another VBA function. InStrRev searches from the end of a string toward the beginning and returns the position of the last matching occurrence, with the position still counted from the start of the original string.

For a worksheet formula, Excel provides FIND and SEARCH. With the text Report-Q4 in A2, the formula =FIND("-",A2) returns 7. FIND is case-sensitive, while SEARCH is not. With the same value in A2, =SEARCH("q",A2) returns 8 because it matches the uppercase Q without regard to case. Both formulas return an error if the target is absent, unlike InStr, which returns 0.

If a worksheet formula should display a message instead of an error when a character is absent, and the searched text is in A2, use =IFERROR(FIND("-",A2),"Not found"). Use a worksheet function when you want a cell result that recalculates with the data; use VBA when the search is part of a macro’s control flow or should trigger an action. Excel’s worksheet FIND and VBA’s InStr are separate tools despite their similar purpose.

Common problems and edge cases

A frequent mistake is treating the result as a character index that begins at zero. VBA string positions begin at 1. Another is assuming a missing target returns False; InStr returns the number 0, so test position > 0 or position = 0. A test such as If position Then may work through VBA’s truth-value conversion, but the explicit numeric comparison is clearer.

Be careful with an empty search string. A search target of zero length does not represent a useful character to find and can produce behavior that is easy to mishandle in repeated-search code. Validate it with Len(needle) > 0 before beginning a loop. Also make sure the requested starting position is at least 1, and do not assume a match exists before using its position with functions such as Mid.

When a cell search fails unexpectedly, check for leading or trailing spaces, nonbreaking spaces, or characters that look alike but have different character codes. A hyphen-minus and an en dash, for example, are distinct characters. If the data is imported or copied from another source, inspect the actual text or normalize it deliberately before searching. Do not remove spaces or punctuation automatically unless that change is appropriate for the data.

Finally, a search for a one-letter substring is not a whole-word search. Searching for cat also matches catalog. If the requirement is to identify a complete word, you need additional boundary checks or a more specialized approach; InStr by itself only checks whether the sequence occurs anywhere in the string.

Frequently Asked Questions

How do I check whether a string contains a character in VBA? Use InStr and test whether its result is greater than 0. A positive result is the character’s starting position; 0 means it was not found.

Is InStr case-sensitive? It depends on the comparison mode. Pass vbBinaryCompare for case-sensitive matching or vbTextCompare for case-insensitive matching rather than relying on a module default.

What does InStr return when the text is missing? It returns 0 when the search target is not present from the specified starting position. Worksheet functions such as FIND and SEARCH instead return an error when they do not find a match.

How can I find the last occurrence of a character? Use InStrRev with the string and target. It searches backward but reports the position using the string’s normal one-based indexing.

Can I use InStr directly in an Excel cell? No. InStr is a VBA function, not a worksheet formula. In a cell, use Excel’s FIND for case-sensitive searching or SEARCH for case-insensitive searching.

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.