VBA InStr Function: 7 Simple and Excel Sheet Examples

What is InStr function in VBA?

InStr is a text/string function that is used to search in a string. It returns (as Variant/Long ) the first occurrence of the searched text/string.

We will show you simple examples of VBA InStr function as well as with Excel data – as this is our focus.

Following is the syntax:

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

So, InStr function takes two strings. See the table below for all arguments:

Possible values for the compare argument are:

This argument is explained in the last example.

An example of InStr function

Let us start with a simple example of using two strings.

We will assign values to these strings after declaration and use both in the InStr function.

The result is displayed in the message box:

VBA code:

Sub InStr_ex()

Dim str1, str2

str1 = "VBA/Excel Tutorials"

str2 = "T"

MsgBox InStr(str1, str2)

End Sub

Result:

So, we searched for the letter ‘T’ in the str1 string. You can see, its first occurrence is at number 11 - that is the returned value of inStr function.

Using start argument

We have this source string:

We will search = “VBA”

The search will start at position 7.

VBA code:

Sub InStr_ex()

Dim str1, str2
Dim res

str1 = "VBA is simple and cool. Like VBA!"

str2 = "VBA"

res = InStr(7, str1, str2)

MsgBox "Occurrence of 'VBA' = " & res

End Sub

Result:

If we omitted the start argument i.e.

str1 = "VBA is simple and cool. Like VBA!"

str2 = "VBA"

res = InStr(str1, str2)

MsgBox "Occurrence of 'VBA' = " & res

Result:

Is InStr case sensitive function?

Consider this string:

We will search for “T” without start argument.

VBA code:

Sub InStr_ex()

Dim str1, str2

Dim res

str1 = "this is Test"

str2 = "T"
res = InStr(str1, str2)

MsgBox "Occurrence of 'T' = " & res

End Sub

Result:

The result proves that inStr is a case-sensitive function.

However, by using compare argument you may perform a case-insensitive search. See the section at the end for compare argument example.

What if the search term is not found?

The inStr function returns 0 if the searched text/string is not found.

See this example:

Sub InStr_ex()

Dim str1, str2

Dim res

str1 = "this is Test"
str2 = "That"


res = InStr(str1, str2)

MsgBox "Occurrence of 'That' = " & res


End Sub

Result:

So, InStr returns 0 if no match is found.

Working with Excel sheet and InStr function

Let us show you the usage of the InStr function in an Excel sheet, we will use the following data:

In the first example, we will create a range of “Product Name” columns.

The task is to search “Product Name” starting with the letter “T” by using InStr function and make all those products color red.

See the code and resultant sheet below:

Sub InStr_ex()

Dim Rng As Range

Dim cell As Range

Dim res

'Creating range of cells

Set Rng = Range("B2:B11")


For Each cell In Rng

    res = InStr(cell, "T")

    If Not res = 0 Then

       'Applying color to Cell which name contains T

       cell.Font.ColorIndex = 3

    End If

Next cell

End Sub

Result:

Making “Out of Stock” rows bold and red

A little more complex example.

If you look at the sample sheet, it contains a Status column with “Out of Stock” values for three products.

The task is to make all those rows red, bold, and italic whose status is “Out of Stock” by using inStr function.

First, have a look at the solution and then a little explanation of how it worked:

VBA code:

Sub InStr_ex()

Dim Rng As Range

Dim cell As Range

Dim res


'Creating range of cells

Set Rng = Range("D2:D11")

For Each cell In Rng

    res = InStr(cell, "Out")

    If Not res = 0 Then

       'Applying color, bold and italic to to whole row

       cell.EntireRow.Font.Bold = True

       cell.EntireRow.Font.Italic = True

       cell.EntireRow.Font.ColorIndex = 3

    End If

Next cell

End Sub

Result:

How did it work?

• First, we created a range of cells for the Status column (D2:D11)
• We used this in For..Each loop
• In each iteration, the InStr function is used to check the value of the cell for the word “Out”
• If it is not 0, that means a match is found then we applied bold, italic, and color to the entire row by using EntireRow

The example of compare argument / case-insensitive search

The compare argument is useful in different scenarios.

In one of the above examples, we saw the InStr function is case-sensitive. By using compare vbTextCompare or its value 1, you may perform a case-insensitive search.

See the example below:

Sub InStr_ex()

Dim str1, str2

Dim res

str1 = "this is Test"

str2 = "T"

res = InStr(1, str1, str2, vbTextCompare)

MsgBox "Occurrence of 'T' = " & res

End Sub

Result:

You see, we searched for the capital ‘T’ and it returned the first occurrence for ‘t’.

You may learn more about InStr function and compare options here:

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function