VBA InStr Function: How to Search for Text in VBA

If you’ve written more than a few VBA macros, you’ve probably hit this moment where you need to check whether some text exists inside a cell. In VBA, InStr is one of those functions you end up using everywhere. It tells you where one piece of text appears inside another. You can use it to check if a text exists in a cell, parse values in a text, or drive conditional logic in macros that depend on text patterns.

In this guide, I will walk you through the InStr syntax, a few examples, how comparison modes work (this part trips people up more than it should), and some patterns you’ll actually reuse.

I recommend you begin with our Excel Fundamentals skill track to build a solid foundation before advancing to VBA or take our Advanced Excel Functions course if you have some experience, to learn more about how functions improve efficiency in data analysis.

What Is the VBA InStr Function?

InStr is a VBA function that returns the position of the first occurrence of one string inside another.

The results behave as follows:

• If found, it returns a number (the position where the match starts)
• If it doesn’t find anything, it returns 0

For example, if “cat” appears at position 5 in a string, you get 5. If it’s not there, you get 0.

VBA InStr Syntax

Here’s the full InStr syntax:

InStr([start], string1, string2, [compare])

Where:

• start (optional): This tells VBA where to begin the search. If you skip it, VBA starts from position 1.

• string1 (required): The full text you’re searching within.

• string2 (required): The text you’re trying to find.

• compare (optional): Defines how the comparison is done (case-sensitive or not). If you leave it out, VBA uses the default comparison mode, usually case-sensitive.

Note that you can ignore start and compare arguments (InStr(string1, string2)) for a simpler version. But if you decide to use the compare argument, you must also provide the start argument.

How VBA InStr Works

Think of InStr as reading a sentence from left to right, one character at a time.

The function starts with string1, scans through it, and looks for the first place where string2 matches. The moment it finds that match, it stops and returns the position where it began.

The “match” position is important since VBA counts from 1, not 0. So the first character in a string is position 1, the second is 2, and so on. 

VBA InStr Return Values Explained

Interpreting the result of an InStr function is straightforward. You can get either of the following:

• Positive number: If it finds a match, and the number tells you where it starts.

• 0: If there is no match anywhere in the string

However, if your input strings are Null, the function returns Null. While you can ignore this for basic macros, it’s worth noting that the difference can break a condition quietly.

Basic VBA InStr Examples

Now let me show you a few examples of how to use the VBA InStr function. 

Find a word in a sentence

To check if the word “before” is inside the note “Deliver before noon”.

Sub Before()

    Dim result As Integer
    result = InStr("Deliver before noon", "before")
    
    MsgBox result

End Sub

The result for the above VBA is 9 since “before” starts at the 9th character.

Check if a character exists

To check if the character “0” exists in a given string, use the VBA:

Sub ChExist()

    Dim result As Integer
    result = InStr(“A1023”, “0”)
    
    MsgBox result

End SubPowered by

In this case, any result greater than 0 means the character has been found.

Search from the beginning of a string

To search from the beginning of a string, you don’t need to specify the start position, so it begins at 1. That’s the default every time unless you say otherwise.

Sub ChExist()

    Dim result As Integer
    result = InStr("Report_2026_Final", "2026")
    
    MsgBox result

End Sub

How to Use VBA InStr in If Statements

When working with a real spreadsheet, you aren't just looking for a number; you are trying to make a decision. The most common way to use InStr is inside an If statement to filter data.

Throughout this tutorial, we will use a sample dataset, Orders, with the columns: ID, CustomerName, Email, City, and OrderNotes.

If InStr(...) > 0 Then

Since InStr returns 0 when it fails to find anything, we check if the result exists when it returns a number greater than 0.

In the example below, it looks at the OrderNotes column (column 5). If it finds the word “Fragile”, it writes “Handle Carefully” in column 6.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        ' Check if the order notes mention "Fragile"
        If InStr(Cells(i, 5).Value, "Fragile") > 0 Then
            Cells(i, 6).Value = "Handle Carefully"
        End If
        
    Next i

End Sub

Conditional checks for keywords and text filtering

You can also use the InStr function to check for keywords in strings, especially if the phrase changes slightly. For example, the code below checks if the note contains “Call” (like “Call on arrival” or “Call before delivery”). It then flags and adds the tag “Cal Customer” in column 6.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        ' Check if the order notes mention “Call”
        If InStr(Cells(i, 5).Value, “Call”) > 0 Then
            Cells(i, 6).Value = “Call Customer”
        End If
        
    Next i

End SubPowered by

VBA InStr Case Sensitivity and Compare Modes

By default, InStr is case-sensitive (called vbBinaryCompare). Let’s say you search for “chicago” but the data says “Chicago” InStr will return 0.

To fix this issue, you use the compare argument where:

• vbBinaryCompare (Default): case-sensitive (“A” is not the same as “a”.)

• vbTextCompare: case-sensitive “A” is the same as “a”.

In the example below, InStr returns 0 since it looks for “fragile” (lowercase) inside “Fragile items”.

Sub CheckFragile()

    Dim result As Integer
    
    result = InStr(1, "Fragile items", "fragile", vbBinaryCompare)
    
    MsgBox result

End Sub

However, using the vbTextCompare returns 1 through the same search but ignores the case.

Sub CheckFragile()

    Dim result As Integer
    
    result = InStr(1, "Fragile items", "fragile", vbTextCompare)
    
    MsgBox result

End Sub

VBA InStr with a Start Position

Sometimes you don't want to start a search at the beginning of the string. The start argument tells VBA where to begin searching.

In the example below, the start argument is set to 10, so VBA begins searching from the 10th character instead of the first. It then finds "delivery" later in the string.

Sub CheckUrgent()

    Dim result As Integer
    
    result = InStr(10, “Deliver before noon - urgent delivery”, “delivery”)
    
    MsgBox result

End SubPowered by

The result of the above example is 31, which is the position where "delivery" begins.

This method is useful when you want the second occurrence of something or to ignore a known prefix when working with repeated patterns.

VBA InStr vs. InStrRev

As you have already learned, InStr searches from left to right. However, InStrRev searches from right to left.

For example, we have used InStrRev to find the last dot in the email. The result is 18, which is the position of the dot before “com”.

Sub CheckChr()

    Dim result As Integer
    
    result = InStrRev("[email protected]", ".")
    
    MsgBox result

End Sub

You will find InStrRev more useful when you need the last occurrence of something or when you’re working with file names, emails, or paths.

Common VBA InStr Use Cases

Now that we have looked at examples of how the VBA InStr function works, let’s look at the common use cases.

Check if a cell contains a keyword

Suppose you have a dataset of customer orders, and you need to flag orders for specific handling. The code looks at the OrderNotes column and flags anything with “noon”.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        If InStr(1, "Deliver before noon", "noon", vbTextCompare) > 0 Then
            ' Logic to mark as "Morning Delivery"
        End If
        
    Next i

End Sub

Validate email-style text

You can also use the InStr function to quickly check valid email formats. For example, the code below checks if the email contains @ and returns “Valid Email Format” in column 6.

Sub ValidateEmail()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        If InStr(Cells(i, 3).Value, "@") > 0 Then
            Cells(i, 6).Value = "Valid Email Format"
        End If
        
    Next i

End Sub

Detecting separators like commas or hyphens

You can use the InStr function to check if a hyphen, comma, or slash exists in text. In the example below, the code loops through OrderNotes (column E, checks if there’s a comma in the text, then writes a result in column F.

Sub ValidateEmail()

    Dim i As Integer
    Dim commaPos As Integer
    Dim OrderNotes As String
    
    For i = 2 To 51
        
        OrderNotes = Cells(i, 5).Value
        
        commaPos = InStr(1, OrderNotes, ",")
        
        ' If a comma exists, mark it
        If commaPos > 0 Then
            Cells(i, 6).Value = "Has comma"
        Else
            Cells(i, 6).Value = "No comma"
        End If
        
    Next i

End Sub

Common Mistakes with VBA InStr

Even as an experienced developer, you may run into some issues with the InStr function, which makes your code behave unexpectedly. The following are the common issues I have encountered and how you can solve them:

• Forgetting InStr is 1-based: Always remember positions start at 1, not 0. If you’re coming from Python or JavaScript, this feels off at first.

• Treating InStr like True/False: Remember that InStr does not return True or False. If you want to find something, you must check if the result is > 0.

• Ignoring case sensitivity: As you now know, InStr is case-sensitive by default. If case doesn’t matter, say so with vbTextCompare in the compare argument.

• Mixing up string1 and string2: Always remember that the first argument is where you search. The second is what you’re looking for. If you search like this InStr(SearchTerm, FullText), you will always get 0.

VBA InStr vs. Other VBA String Functions

Since InStr usually shows where a string is in a text, let’s compare it with other string functions.

InStr vs. Like

The Like operator is used for pattern matching. For example, the code below checks for “Fragile”, but uses a pattern *, which means “anything before or after”.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        If Cells(i, 5).Value Like "*Fragile*" Then
            Cells(i, 6).Value = "Handle Carefully"
        End If
        
    Next i

End Sub

Use InStr if you just need to know if a text exists, and Like when you need to check for text in complex patterns.

InStr vs. Replace

While you can use InStr to change the whole value in a cell, the Replace function changes specific text in the cell.

In the example below, the Replace function directly swaps “Fragile” with “Handle Carefully” inside the text.

Sub ReplaceOrder()

    Dim i As Integer
    
    For i = 2 To 51
        Cells(i, 5).Value = Replace(Cells(i, 5).Value, "Fragile", "Handle Carefully")
    Next i

End Sub

InStr vs. Mid

The Mid function is used to extract the text from a string. Therefore, you’ll often use Mid with InStr to extract the text after getting its position. 

The code below extracts the domain from emails in the dataset. InStr function finds where "@" appears in the email, then Mid starts extraction after it. The code ends by writing the domain into column H.

Sub ExtractDomain()

    Dim i As Integer
    Dim pos As Integer
    Dim domain As String
    
    For i = 2 To 51
        
        pos = InStr(1, Cells(i, 3).Value, "@")
        
        If pos > 0 Then
            domain = Mid(Cells(i, 3).Value, pos + 1)
            Cells(i, 8).Value = domain
        End If
        
    Next i

End Sub

InStr vs.Split

The Split function is used to break text into parts by specifying a separator. 

In the example below, the code reads each row from column A, checks if there’s a comma, and then splits the text into two parts. It writes Name → column B and City → column C.

Sub SplitTextColumn()

    Dim i As Integer
    Dim lastRow As Integer
    Dim parts As Variant
    Dim text As String
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        
        text = Cells(i, 1).Value
        
        ' Only split if a comma exists
        If InStr(1, text, ",") > 0 Then
            
            parts = Split(text, ",")
            
            Cells(i, 2).Value = parts(0) ' Name
            Cells(i, 3).Value = parts(1) ' City
            
        End If
        
    Next i

End Sub

Best Practices for Using VBA InStr

To avoid getting errors in your VBA, I recommend the following best practices, which have helped me save time:

• Use vbTextCompare when case doesn’t matter: Since most real data isn’t consistent with casing, you may miss matches if you rely on the default compare argument.

• Combine with “If” for clear logic: Always try to include a logic to return a value, since InStr on its own just gives you a number.

• Check for > 0, not exact positions, unless you actually need the position.

• Keep parsing readable. Don’t stack too many functions in one line.

Conclusion

InStr is simple, but you’ll end up using it everywhere. It gives you the position of one string inside another, and that single number drives a lot of decisions in VBA, whether something exists, where to extract from, and what a macro should do next. On its own, it’s just a search. Combined with functions like Mid, Replace, or even Split, it becomes part of a workflow that actually gets things done.

Take our Data Analysis in Excel course to master advanced analytics. Also, takie our Intermediate Power Query in Excel course to learn about data transformation and using the M language.