VBA InStrRev Function

Purpose of InStrRev function in VBA

The Excel/VBA InStrRev function (In String Reverse), is just like the InStr function except for it searches the given term from the end of the given string.

It returns the position of the occurrence of the searched term in the given string – from the end.

Syntax:

InstrRev(stringcheck, stringmatch, [ start, [ compare ]])

Let us show you examples of InStrRev function with various arguments.

An example of InstrRev with required arguments

Only the first two arguments are required for using the InstrRev function. These are:

• stringcheck
• stringmatch
• The stringcheck is the source string that you want to check in.
• Stringmatch is the search term.

The following example makes it clear. In the example below, we have two string variables.

The first is the source string and the other contains the search term.

For showing the difference, we will pass these two strings to both InStr and InStrRev functions.

The code:

Sub InStrRev_ex()

Dim str1, str2

str1 = "The best school in Town"

str2 = "T"

MsgBox "Result with InStr = " & InStr(str1, str2)

MsgBox "Result with InStrRev = " & InStrRev(str1, str2)

End Sub

Results:

And

How did it work?

• So, we searched for the letter ‘T’ in the string.
• The string is “The best school in Town”.
• ‘T’ occurs as the first letter as well as 20^th
• As InStr returns the first occurrence from the start, so we got 1.
• InStrRev returns the first occurrence from the end, so we got 20^th

Using the start argument example

In this example, we will use a third optional argument, start in the InStrRev function.

It allows us to specify the starting position of the source string. For example, we have the following source string:

“The best school in Town”

This string has 23 characters.

We want to search the occurrence of ‘T’ starting from the letter 16, i.e. school’s ‘l’ backward.

The code:

Sub InStrRev_ex()

Dim str1, str2

str1 = "The best school in Town"
str2 = "T"

MsgBox "Result with InStrRev = " & InStrRev(str1, str2, 16)


End Sub

Output:

If we used 21 for the start argument, the result would be:

20

Is InStrRev perform a case-sensitive search?

Let us check in the example below:

Sub InStrRev_ex()

Dim str1
str1 = "Test test"

MsgBox "Result for 'T' = " & InStrRev(str1, "T")

MsgBox "Result for 't' = " & InStrRev(str1, "t")

End Sub

Results:

and

So, it’s case-sensitive by default.

How to perform a case-insensitive search by the fourth argument

As we have seen in the syntax, the fourth optional argument is compare in the InStrRev function.

This argument in the inStrRev function allows us to perform case-insensitive searches.

Though, it has more options than just case-insensitive. You may read more about it here.

The example below uses vbTextCompare constant and see the output:

Sub InStrRev_ex()

Dim str1
str1 = "Test test"


MsgBox "Result for 'T' = " & InStrRev(str1, "T", , vbTextCompare)

MsgBox "Result for 't' = " & InStrRev(str1, "t", , vbTextCompare)

End Sub

Output:

You can see, searching for ‘T’ found in position 9 this time – which is ‘t’.

Using Excel cells in InStrRev function

We have a range of A2:A10 cells.

We will search the position of ‘e’ in the Product column (A).

A few cells are empty and one product does not contain the letter ‘e’.

The result below shows cell data and returned position by the InStrRev function:

Sub InStrRev_ex()

Dim rng As Range
Dim i

i = 2
Set rng = Range("A2:A10")

For Each cell In rng

    Range("B" & i).Value = InStrRev(cell, "e")

    i = i + 1

Next

End Sub

Output: