How to use InStrRev Function in Excel VBA Easily (3 Example)

Welcome to Excel Avon

WHAT IS INSTRREV FUNCTION?

DOWNLOAD THE USED EXCEL FILE FROM HERE>>

So, today’s post How to Use InstrRev Functions in Excel VBA Use the VBA InstrRev Function to find the position of a given substring within a given string in VBA. But the InStrRev function’s search starts from the end of the string, but the return position is counted from the beginning of the string. The value of the InStrRev function is an integer.

To understand for InstrRev Function in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

THE SYNTAX OF INSTRREV FUNCTION

InStrRev([string1], [string2], [start], [Compare as Vbcomaparemethod = VbBinaryCompare])

Arguments

String1 – string1 is the actual string within which the substring is to be found. string value is required.

String2 – String2 is the substring to be found. substring2 value is required.

Start – [Optional] Start is the position from where the function starts searching.

Compare – [Optional] Compare is the type of comparison to be performed.

VbBinaryCompare -This is a binary comparison and can be entered as zero (0). It is a case-sensitive search of the string2 in the actual string 1.

VbTextCompare – It is a case-insensitive search of the “string 2” in the “string 1.”

VbUseCompareOption – It is used to do option compare.

To understand InstrRev Function in Excel VBA, we will write a subroutine for InStrRev Function in Excel VBA after inserting the module in VBE.

Sub UseInStrRev()

End Sub

After writing the subroutine we will define the variable 

Sub UseInStrRev()
Dim InpStr as String
Dim i as Long

End Sub

After we Define the variable, we will write some text to use in function.

Sub UseInStrRev() 
Dim InpStr as String
Dim i As Long 

InpStr = "Raja is good person but not good at all"

End Sub

Now we find the value of last ‘good’ of string text we will use InStrRev function to find the value.

Sub UseInStrRev()
Dim InpStr as String
Dim i As Long

InpStr = "Raja is good person but not good at all"
i = InStrRev(InpStr, "good")
End Sub

And now we will use debug.Print to print the value result in the Immediate window

Sub UseInStrRev() 
Dim InpStr as String 
Dim i As Long 

InpStr = "Raja is good person but not good at all"

i = InStrRev(InpStr, "good")
debug.Print i
End Sub

Click on run button

Now we can see in the Immediate window that we have printed the value of g of the word ‘good’.

Now we found the position of the first ‘good’ from the last in the string text, we want to find second ‘good’ word position from the last. So we will write like below

Sub UseInStrRev()
Dim InpStr as String 
Dim i As Long 

InpStr = "Raja is good person but not good at all"

i = InStrRev(InpStr, "good", 30) 
End Sub

Sub UseInStrRev()
Dim InpStr as String 
Dim i As Long 

InpStr = "Raja is good person but not good at all"

i = InStrRev(InpStr, "good", 30)
debug.Print i
End Sub

Click on run button

Now we can see in the Immediate Window This returns first ‘good’ word position its first letter position for word ‘g’.

We can also find the position by making some changes in the third Criteria[start] (by putting position of first ‘good’ from the last and subtracting 1).

Sub UseInStrRev() 
Dim InpStr as String 
Dim i As Long 

InpStr = "Raja is good person but not good at all"

i = InStrRev(InpStr, "good", i-1) 
debug.Print i 
End Sub

Click on run button

Now we can see in the Immediate Window But now by changing the third criteria, the position of ‘g’ in ‘good’ is printed.

Suppose you have to print the value of ‘GOOD’ yes you will say that you have already got the value of ‘good’ but if you look you will see that if you try to extract the position of ‘GOOD’ you will get the value zero. Since the case has changed, let’s run the code one more time and see.

Sub UseInStrRev() 
Dim InpStr as String 
Dim i As Long 

InpStr = "Raja is good person but not good at all" 

i = InStrRev(InpStr, "Good", 30) 
debug.Print i 
End Sub

As you can see the position of ‘GOOD’ is zero. Zero means can’t find value in string if case is changed.

To find the value even after changing the case we will use vbTextCompare.

Sub UseInStrRev() 
Dim InpStr as String 
Dim i As Long 

InpStr = "Raja is good person but not good at all" 

i = InStrRev(InpStr, "Good", 30, vbTextCompare) 
debug.Print i 
End Sub

Now we can see in the Immediate Window We can use vbTextCompare to print the position of the text written in any case.

        It is a case-sensitive function. To eliminate this issue, supply the “compare” argument “vbTextCompare.”