VBA InStrRev Function

Purpose of InStrRev function in VBA

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

Syntax:

Only the first two arguments are required to use the InstrRev function.

These are:

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

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

An example of InstrRev with required arguments

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.

To show 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

Using the start argument example

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

For example, we have the following source string:

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:

Does 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

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.

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: