Beginner’s Guide to the InStr Function in VBA

While you’re working with textual content in VBA, typically the toughest half is just determining the place one thing is hiding. A single house within the unsuitable place, a lacking character, or a phrase buried in an extended string can throw every part off.

The excellent news is there’s a easy operate that may assist, as a result of it handles the looking for you, so you may give attention to what to do with the end result. You simply must know tips on how to set it up and use it.

So on this information, I’ll stroll by way of what InStr is in VBA, the way it works, tips on how to use it correctly, and tips on how to sidestep the errors that usually journey folks up.

Sidenote: Wish to study extra about tips on how to use VBA and take your Excel expertise to the following degree? Check out my complete VBA course:

That is the one course it is advisable to study VBA Programming and grasp information evaluation with Excel. You may study VBA Macros, Variables, Userforms, Loops, Arrays, Pivot Tables, and far more, so that you’ve the abilities it is advisable to begin a profession in Information Analytics and get employed!

Not dangerous proper?

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

What’s InStr and what does it do?

You may consider  InStr as VBA’s very personal built-in search software for textual content. 

As a substitute of you manually scanning by way of a whole lot of traces of data, InStr goes by way of the textual content for you after which tells you whether or not the factor you care about is in there.

So why is that this so vital?

Properly not solely can this operate scan textual content robotically, however as soon as it finds what’s there, you may then make VBA do one thing with it. Like transferring a message to a monitoring sheet, flagging it for follow-up, or sorting it into the best class.

For instance

Think about you’re employed at a giant e-commerce firm that sells hundreds of skincare merchandise.

Due to this excessive quantity, clients are consistently emailing in with questions, compliments, random suggestions, and – from time to time – calls for for a “refund.”

Right here’s the factor although: 

Most of these clients don’t really need their a refund. They simply need the problem fastened shortly. So when you catch these messages instantly and deal with them quick, you often preserve the client glad and dependable.

The issue in fact is that digging by way of a crowded inbox each morning simply to search out these refund emails can be an enormous time-sink. 

The excellent news is that we are able to work smarter, not more durable!

Relatively than manually undergo all of these emails manually with the intention to discover the refund points, you might have VBA scan incoming messages for the phrase “refund”, after which robotically drop them right into a “Refunds” sheet. 

Then you may have a devoted buyer help particular person use that sheet to get to work dealing with refunds. It’ll replace every day, they usually can preserve observe of who they’ve handled and their standing.

Useful proper?

That’s InStr in motion. It’s like giving Excel a pair of eyes that may spot the phrases you care about.

So let’s see how we may make this occur.

The right way to use InStr

The bottom InStr operate seems like this:

InStr(begin, stringToSearch, stringToFind)

So let’s break this down:

Begin

That is the place within the textual content you need VBA to start wanting. More often than not you’ll simply put 1 right here, which means “begin from the start/very first character”

stringToSearch

That is the textual content part that you just’re looking by way of

stringToFind

That is the piece of textual content you’re attempting to find, like “refund”for instance

Simple proper?

Now clearly in an actual utility we’d add extra code after that, in order that if the goal phrase is discovered it might then set off a selected motion.

For instance

The code block beneath will undergo every of our emails after which seek for the phrase “refund”. If it finds it, it’ll then add it to a spreadsheet known as, surprisingly sufficient, ‘Refunds’. 

If the phrase is not there then again, the code will skip proper to the following electronic mail and verify it in the identical manner, and so forth and so forth.

If InStr(1, emailSubject, "refund") > 0 Then
    ' Discover the following empty row within the Refunds sheet
    Dim nextRow As Lengthy
    nextRow = Sheets("Refunds").Cells(Rows.Rely, "A").Finish(xlUp).Row + 1

    ' Copy the topic into column A
    Sheets("Refunds").Cells(nextRow, 1).Worth = emailSubject
Finish If

And refunds are simply one use case.

You possibly can additionally use InStr to:

…as just some attainable examples!

Principally, any time you wish to discover particular phrases and robotically manage them, InStr is your good friend.

And when you’ve integrated it into your code, it’s only a matter of tweaking the phrase or phrase you care about. Higher nonetheless, we are able to even take this a step additional and run extra superior searches and filters.

#1. The right way to search completely different sections of the information

To this point, we’ve solely checked the e-mail topic traces, but it surely could possibly be that the client talked about our goal phrase within the physique textual content. So we are able to attempt looking there as an alternative!

For instance

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

    ' Copy topic and physique aspect by aspect
    Sheets("Refunds").Cells(nextRow, 1).Worth = emailSubject
    Sheets("Refunds").Cells(nextRow, 2).Worth = emailBody
Finish If

Tremendous easy.

#2. The right way to seek for a number of phrases directly

Prospects don’t all the time use the very same phrases to say the identical factor; some might say “refund”, whereas others would possibly say “return” or “alternate”. And when you solely seek for one time period, you’ll miss the others.

Fortunately, you may chain InStr checks collectively utilizing VBA’s Or operator. This permits VBA to search for any of the phrases you care about, and take motion if no less than one matches.

For instance

Right here we are able to catch both refunds or returns within the topic line:

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

    ' Copy the topic line into the Refunds sheet
    Sheets("Refunds").Cells(nextRow, 1).Worth = emailSubject
Finish If

The underside line is, this method retains you from shedding sight of circumstances the place the client phrases issues just a little in a different way. 

#3. The right way to mix these right into a deeper search

In a real-world situation, you’d in all probability wish to mix each of the choices we noticed over the past two examples. This could allow your code to seek for a number of phrases, whereas additionally checking for them in each the headline AND physique textual content.

Thankfully, we are able to accomplish this with out a lot further hassle.

For instance

The code block beneath will search throughout each topic and physique textual content for the phrases “refund”, “sad”, and “downside”, after which seize any matching topics/our bodies right into a mixed sheet known as “Refunds_Complaints”:

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

    ' Copy the topic line into the Refunds sheet
    Sheets("Refunds").Cells(nextRow, 1).Worth = emailSubject
Finish If

Useful proper?

And naturally, you may simply add to those examples as you go for different phrases that you just discover your viewers makes use of.

#4. The right way to kind into completely different sheets robotically

We’ve already seen examples of refund-type points, however wouldn’t or not it’s helpful if we may additionally observe different phrases – pertaining to differing types of points altogether – which may set off completely different required actions?

For instance

To complicate our refund situation a bit, let’s have a go at monitoring pressing points and complaints as nicely.

Particularly:

• Refunds ought to go to a Refunds tab in your sheet

• Pressing points ought to go to a Precedence tab (to allow them to be handled earlier than they turn into a attainable refund problem)

• Common complaints ought to go to a Complaints tab ( so you may go this on to enhance the services or products)

Whereas it could sound a bit daunting, you may get this performed by merely chaining a number of InStr checks with ElseIf, like so:

If InStr(1, emailSubject, "refund") > 0 Or InStr(1, emailBody, "refund") > 0 Then
    Dim nextRow As Lengthy
    nextRow = Sheets("Refunds").Cells(Rows.Rely, "A").Finish(xlUp).Row + 1
    Sheets("Refunds").Cells(nextRow, 1).Worth = emailSubject
    Sheets("Refunds").Cells(nextRow, 2).Worth = emailBody

ElseIf InStr(1, emailBody, "pressing") > 0 Then
    nextRow = Sheets("Precedence").Cells(Rows.Rely, "A").Finish(xlUp).Row + 1
    Sheets("Precedence").Cells(nextRow, 1).Worth = emailSubject
    Sheets("Precedence").Cells(nextRow, 2).Worth = emailBody

ElseIf InStr(1, emailBody, "sad") > 0 Or InStr(1, emailBody, "downside") > 0 Then
    nextRow = Sheets("Complaints").Cells(Rows.Rely, "A").Finish(xlUp).Row + 1
    Sheets("Complaints").Cells(nextRow, 1).Worth = emailSubject
    Sheets("Complaints").Cells(nextRow, 2).Worth = emailBody
Finish If

Now every kind of electronic mail robotically goes to the best “lane,” so your teammates can give attention to what they care about most.

• Refund specialist? Open the Refunds tab

• Triage agent? Open the Precedence tab

• Criticism handler? Open Complaints

All from the identical code scanning these emails!

#5. The right way to do “position-aware” checks when wanted

More often than not, you don’t care the place a phrase reveals up, solely that it exists. However typically, the place of the phrase within the total textual content string is important.

For instance

Log information will usually prefix critical points with a tag like Error: at the beginning of a line. That’s completely different from an informal point out of “error” later in a message, so you’d clearly wish to flag such points in a different way as nicely.

And we are able to do exactly that by specifying the precise place of the goal phrase – as a further argument to InStr – like so:

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

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

Be aware how the primary argument specifies:

= 1

Guaranteeing that any matching outcomes should be the primary phrase of the search textual content.

We may in fact change that “1” to any quantity we like, relying on the circumstance, however on this specific case we all the time wish to goal that first phrase.

Widespread errors with InStr and tips on how to repair them

Though InStr is fairly easy, there are two “gotchas” that are likely to journey folks up after they first begin utilizing it.

So let’s break them down actual fast.

#1. Case sensitivity journeys you up

By default, InStr is case-sensitive. Which means when you seek for “Refund” however the buyer writes “refund” with out the capital “R”, your code will miss it. Identical goes for “RETURN” or “Return”; VBA will deal with all

The excellent news is, there’s a fast answer we are able to add to our code to account for these variations: vbTextCompare.

For instance

If we edit our code like so:

If InStr(1, emailSubject, "refund", vbTextCompare) > 0 Then
    ' Log the e-mail or do the factor and so forth
Finish If

Now all 3 variations of “refund”, “Refund”, and “REFUND” will all be handled as the identical. That is now a lot safer, particularly for messy real-world information.

#2. Not looking for typos and inconsistent spelling

Spelling errors and typos out of your customers are sure to occur.

The problem in fact is that in case your code solely searches for the proper spelling, you’re going to overlook emails that ought to be flagged, simply because they’ve typos.

For instance

Let’s say you solely seek for “refund”. That works advantageous if the client sorts it completely!

However what if their message says “refun”, “refand”, or “refnd”? Properly, your script will fully skip over these, though the client clearly meant “refund”.

VBA sadly doesn’t have a built-in option to do a “fuzzy match”, or in any other case catch typos robotically. The repair is easy although – you may simply add a couple of of the commonest misspellings to your InStr checks.

Right here’s how which may 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 Lengthy
    nextRow = Sheets("Refunds").Cells(Rows.Rely, "A").Finish(xlUp).Row + 1

    Sheets("Refunds").Cells(nextRow, 1).Worth = emailSubject
    Sheets("Refunds").Cells(nextRow, 2).Worth = emailBody
Finish If

That is definitely extra effort, and is usually a little bit of a ache, but it surely works. Truthfully although, you’ll be stunned on the proportion of text-matching situations that will likely be affected by this precise problem, so it’s often a step value taking.

Time to present InStr a go in your personal code!

Whereas InStr would possibly look easy, when you begin utilizing it you’ll shortly see how a lot time it saves. As a substitute of searching by way of textual content by hand, you may let VBA do the legwork and simply give attention to what to do with the outcomes. 

The trick is remembering the little pitfalls we coated, like case sensitivity and typos, so that you don’t by accident miss stuff you shouldn’t. And one of the best ways to recollect them, is to attempt InStr out for your self.…so bounce proper in and provides this a go!

Attempt adapting the examples we walked by way of to assist sift by way of your inbox, and even make sense of a messy spreadsheet! When you see InStr working in your personal information, you’ll get the dangle of it quick, and begin recognizing all kinds of the way to make use of it.

P.S.

And keep in mind, if you wish to study extra about tips on how to use VBA and take your Excel expertise to the following degree, check out my complete VBA course:

That is the one course it is advisable to study VBA Programming and grasp information evaluation with Excel. Study VBA Macros, Variables, Userforms, Loops, Arrays, Pivot Tables, and far more. 

This course will provide you with the abilities it is advisable to begin a profession in Information Analytics and get employed!

And as an added bonus, when you be part of you’ll get entry to our non-public Discord neighborhood:

You may ask questions from me, different college students, and chat with working tech professionals.