Beginner’s Guide to the InStr Function in VBA

When you’re working with text in VBA, sometimes the hardest part is simply figuring out where something is hiding. A single space in the wrong place, a missing character, or a word buried in a long string can throw everything off.

The good news is there’s a simple function that can help, because it handles the searching for you, so you can focus on what to do with the result. You just need to know how to set it up and use it.

So in this guide, I’ll walk through what InStr is in VBA, how it works, how to use it properly, and how to sidestep the mistakes that often trip people up.

Sidenote: Want to learn more about how to use VBA and take your Excel skills to the next level? Check out my complete VBA course:

Advanced

VBA Programming Bootcamp: Zero to Mastery

11 Hours •75 Lessons

Become a data analytics pro by using VBA (Visual Basic for Applications) Programming in Excel. Learn VBA Macros, Variables, Userforms, Loops, Arrays, and Pivot Tables. Build your career in data analysis & get hired.

Travis Cuzick

This is the only course you need to learn VBA Programming and master data analysis with Excel. You'll learn VBA Macros, Variables, Userforms, Loops, Arrays, Pivot Tables, and much more, so that you have the skills you need to start a career in Data Analytics and get hired!

Not bad right?

With that out of the way, let’s get into this 5-minute tutorial…

What is InStr and what does it do?

You can think of  InStr as VBA’s very own built-in search tool for text. 

Instead of you manually scanning through hundreds of lines of information, InStr goes through the text for you and then tells you whether the thing you care about is in there.

So why is this so important?

Well not only can this function scan text automatically, but once it finds what’s there, you can then make VBA do something with it. Like moving a message to a tracking sheet, flagging it for follow-up, or sorting it into the right category.

For example

Imagine you work at a big e-commerce company that sells thousands of skincare products.

Because of this high volume, customers are constantly emailing in with questions, compliments, random feedback, and - every now and then - demands for a “refund.”

Here’s the thing though: 

Most of those customers don’t actually want their money back. They just want the issue fixed quickly. So if you catch those messages right away and handle them fast, you usually keep the customer happy and loyal.

The problem of course is that digging through a crowded inbox every morning just to find those refund emails would be a huge time-sink. 

The good news is that we can work smarter, not harder!

Rather than manually go through all of those emails manually so that you can find the refund issues, you could have VBA scan incoming messages for the word “refund”, and then automatically drop them into a “Refunds” sheet. 

Then you can have a dedicated customer support person use that sheet to get to work handling refunds. It’ll update each day, and they can keep track of who they’ve dealt with and their status.

Handy right?

That’s InStr in action. It’s like giving Excel a pair of eyes that can spot the words you care about.

So let’s see how we could make this happen.

How to use InStr

The base InStr function looks like this:

InStr(start, stringToSearch, stringToFind)

So let’s break this down:

Start

This is where in the text you want VBA to begin looking. Most of the time you’ll just put 1 here, meaning “start from the beginning/very first character”

stringToSearch

This is the text section that you’re searching through

stringToFind

This is the piece of text you’re hunting for, like "refund"for example

Easy right?

Now obviously in a real application we would add more code after that, so that if the target word is found it would then trigger a specific action.

For example

The code block below will go through each of our emails and then search for the word “refund”. If it finds it, it will then add it to a spreadsheet called, surprisingly enough, ‘Refunds’. 

If the word is not there on the other hand, the code will skip right to the next email and check it in the same way, and so on and so on.

If InStr(1, emailSubject, "refund") > 0 Then
    ' Find the next empty row in the Refunds sheet
    Dim nextRow As Long
    nextRow = Sheets("Refunds").Cells(Rows.Count, "A").End(xlUp).Row + 1

    ' Copy the subject into column A
    Sheets("Refunds").Cells(nextRow, 1).Value = emailSubject
End If

You could also use InStr to:

• Flag VIP customer emails

• Pull in error reports from logs

• Organize order confirmations

• Clean up messy text data

…as just a few possible examples!

Basically, any time you want to find specific words and automatically organize them, InStr is your friend.

And once you’ve incorporated it into your code, it’s just a matter of tweaking the word or phrase you care about. Better still, we can even take this a step further and run more advanced searches and filters.

#1. How to search different sections of the data

So far, we’ve only checked the email subject lines, but it could be that the customer mentioned our target word in the body text. So we can try searching there instead!

For example

If InStr(1, emailBody, "refund") > 0 Then
    Dim nextRow As Long
    nextRow = Sheets("Refunds").Cells(Rows.Count, "A").End(xlUp).Row + 1

    ' Copy subject and body side by side
    Sheets("Refunds").Cells(nextRow, 1).Value = emailSubject
    Sheets("Refunds").Cells(nextRow, 2).Value = emailBody
End If

Super simple.

#2. How to search for multiple terms at once

Customers don’t always use the exact same words to say the same thing; some may say “refund”, while others might say “return” or “exchange". And if you only search for one term, you’ll miss the others.

Luckily, you can chain InStr checks together using VBA’s Or operator. This allows VBA to look for any of the words you care about, and take action if at least one matches.

For example

Here we can catch either refunds or returns in the subject line:

If InStr(1, emailSubject, "refund") > 0 Or InStr(1, emailSubject, "return") > 0 Then
    Dim nextRow As Long
    nextRow = Sheets("Refunds").Cells(Rows.Count, "A").End(xlUp).Row + 1

    ' Copy the subject line into the Refunds sheet
    Sheets("Refunds").Cells(nextRow, 1).Value = emailSubject
End If

#3. How to combine these into a deeper search

In a real-world scenario, you would probably want to combine both of the options we saw over the last two examples. This would enable your code to search for multiple terms, while also checking for them in both the headline AND body text.

Fortunately, we can accomplish this without much additional trouble.

For example

The code block below will search across both subject and body text for the words “refund”, “unhappy”, and “problem”, and then capture any matching subjects/bodies into a combined sheet called “Refunds_Complaints”:

If InStr(1, emailSubject, "refund") > 0 Or InStr(1, emailSubject, "return") > 0 Then
    Dim nextRow As Long
    nextRow = Sheets("Refunds").Cells(Rows.Count, "A").End(xlUp).Row + 1

    ' Copy the subject line into the Refunds sheet
    Sheets("Refunds").Cells(nextRow, 1).Value = emailSubject
End If

Handy right?

And of course, you can easily add to these examples as you go for other words that you notice your audience uses.

#4. How to sort into different sheets automatically

We’ve already seen examples of refund-type issues, but wouldn’t it be handy if we could also track other words - pertaining to different sorts of issues altogether - that might trigger different required actions?

For example

To complicate our refund scenario a bit, let’s have a go at tracking urgent issues and complaints as well.

Specifically:

• Refunds should go to a Refunds tab on your sheet

• Urgent issues should go to a Priority tab (so they can be dealt with before they become a possible refund issue)

• General complaints should go to a Complaints tab ( so you can pass this on to improve the product or service)

While it may sound a bit daunting, you can get this done by simply chaining multiple InStr checks with ElseIf, like so:

If InStr(1, emailSubject, "refund") > 0 Or InStr(1, emailBody, "refund") > 0 Then
    Dim nextRow As Long
    nextRow = Sheets("Refunds").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Refunds").Cells(nextRow, 1).Value = emailSubject
    Sheets("Refunds").Cells(nextRow, 2).Value = emailBody

ElseIf InStr(1, emailBody, "urgent") > 0 Then
    nextRow = Sheets("Priority").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Priority").Cells(nextRow, 1).Value = emailSubject
    Sheets("Priority").Cells(nextRow, 2).Value = emailBody

ElseIf InStr(1, emailBody, "unhappy") > 0 Or InStr(1, emailBody, "problem") > 0 Then
    nextRow = Sheets("Complaints").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Sheets("Complaints").Cells(nextRow, 1).Value = emailSubject
    Sheets("Complaints").Cells(nextRow, 2).Value = emailBody
End If

• Refund specialist? Open the Refunds tab

• Triage agent? Open the Priority tab

• Complaint handler? Open Complaints

All from the same code scanning those emails!

#5. How to do “position-aware” checks when needed

Most of the time, you don’t care where a word shows up, only that it exists. But sometimes, the position of the word in the overall text string is critical.

For example

Log files will often prefix serious issues with a tag like Error: at the start of a line. That’s different from a casual mention of “error” later in a message, so you would obviously want to flag such issues differently as well.

And we can do just that by specifying the exact position of the target word - as an additional argument to InStr - like so:

If InStr(1, logMessage, "Error:") = 1 Then
    Dim nextRow As Long
    nextRow = Sheets("Errors").Cells(Rows.Count, "A").End(xlUp).Row + 1

    ' Copy the log line into the Errors sheet
    Sheets("Errors").Cells(nextRow, 1).Value = logMessage
End If

= 1

Ensuring that any matching results must be the first word of the search text.

We could of course change that “1” to any number we like, depending on the circumstance, but in this particular case we always want to target that first word.

Common mistakes with InStr and how to fix them

Even though InStr is pretty simple, there are two “gotchas” that tend to trip people up when they first start using it.

So let’s break them down real quick.

#1. Case sensitivity trips you up

By default, InStr is case-sensitive. That means if you search for "Refund" but the customer writes "refund" without the capital “R”, your code will miss it. Same goes for "RETURN" or "Return"; VBA will treat all

The good news is, there’s a quick solution we can add to our code to account for these variations: vbTextCompare.

For example

If we edit our code like so:

If InStr(1, emailSubject, "refund", vbTextCompare) > 0 Then
    ' Log the email or do the thing etc
End If

Now all 3 versions of "refund", "Refund", and "REFUND" will all be treated as the same. This is now much safer, especially for messy real-world data.

#2. Not searching for typos and inconsistent spelling

Spelling mistakes and typos from your users are bound to happen.

The issue of course is that if your code only searches for the correct spelling, you’re going to miss emails that should be flagged, just because they have typos.

For example

Let’s say you only search for "refund". That works fine if the customer types it perfectly!

But what if their message says "refun", "refand", or "refnd"? Well, your script will completely skip over those, even though the customer obviously meant “refund”.

VBA unfortunately doesn’t have a built-in way to do a “fuzzy match”, or otherwise catch typos automatically. The fix is simple though - you can just add a few of the most common misspellings to your InStr checks.

Here’s how that might look:

If InStr(1, emailBody, "refund") > 0 _
   Or InStr(1, emailBody, "refun") > 0 _
   Or InStr(1, emailBody, "refnd") > 0 _
   Or InStr(1, emailBody, "refand") > 0 Then

    Dim nextRow As Long
    nextRow = Sheets("Refunds").Cells(Rows.Count, "A").End(xlUp).Row + 1

    Sheets("Refunds").Cells(nextRow, 1).Value = emailSubject
    Sheets("Refunds").Cells(nextRow, 2).Value = emailBody
End If

Time to give InStr a go in your own code!

While InStr might look simple, once you start using it you’ll quickly see how much time it saves. Instead of hunting through text by hand, you can let VBA do the legwork and just focus on what to do with the results. 

The trick is remembering the little pitfalls we covered, like case sensitivity and typos, so you don’t accidentally miss things you shouldn’t. And the best way to remember them, is to try InStr out for yourself.…so jump right in and give this a go!

Try adapting the examples we walked through to help sift through your inbox, or even make sense of a messy spreadsheet! Once you see InStr working in your own files, you’ll get the hang of it fast, and start spotting all sorts of ways to use it.

P.S.

And remember, if you want to learn more about how to use VBA and take your Excel skills to the next level, check out my complete VBA course:

Advanced

VBA Programming Bootcamp: Zero to Mastery

11 Hours •75 Lessons

Become a data analytics pro by using VBA (Visual Basic for Applications) Programming in Excel. Learn VBA Macros, Variables, Userforms, Loops, Arrays, and Pivot Tables. Build your career in data analysis & get hired.

Travis Cuzick

This is the only course you need to learn VBA Programming and master data analysis with Excel. Learn VBA Macros, Variables, Userforms, Loops, Arrays, Pivot Tables, and much more. 

This course will give you the skills you need to start a career in Data Analytics and get hired!

And as an added bonus, once you join you’ll get access to our private Discord community:

You can ask questions from me, other students, and chat with working tech professionals.