You are reading the article **How To Use Excel Vba Instrrev With Examples?** updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. *Suggested October 2023 How To Use Excel Vba Instrrev With Examples?*

Knowing the occurrence of a string in another string can be very handy while working with day to day data. Obviously, we can do it manually by calculating the occurrence of the string in another string but that would the task very hefty. So to make it easier we have a function in VBA which is known as INSTRREV which is used to find the occurrence.

As explained above, INSTRREV in Excel VBA is used to find an occurrence of a string in another string. This function finds the first occurrence of a string in the target string and returns the value. Now we have to remember that as it gives the occurrence of the string so the returned value is numeric. Also as it is a comparison function so like other functions in VBA there are three basic comparisons methods.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of InStrRev in Excel VBAThe syntax for VBA InStrRev function in excel is as follows:

Now let us break down the syntax and learn about it, String is the main string from where we want to find the occurrence of a substring, Start is the numeric occurrence we provide to the string. If no start parameter is provided the function starts looking a string from the end of it. And compare is the comparison method we provide to the function. There are three types of comparison for this function:

To use Option Compare which is (-1). It is also known as VbUseCompareOption.

To use Binary Compare which is (0). It is also known as VbBinaryCompare.

To use Text Compare which is (1). It is also known as VbTextCompare.

Again if none of the compare options is provided then the function automatically considers it as a binary compare.

Now let us use this function in a few examples and look at how to use this function.

How to Use Excel VBA InStrRev?Now let us try with some examples on VBA InStrRev in Excel.

You can download this VBA InStrRev Excel Template here – VBA InStrRev Excel Template

Example #1 – VBA InStrRevStep 2: Once we enter the VB editor we can see in the header section, there is an option of insert. Insert a new module from that option as shown below.

Step 3: Now let us start our subprocedure in the module as shown below.

Code:

Sub

Sample()End Sub

Step 4: Now declare a variable as an integer which will hold the output value of the function for us.

Code:

Sub

Sample()Dim

AAs Integer

End Sub

Step 5: Now in the variable use the INSTRREV function to find the occurrence of “ “ in the string “ I am a Good Boy” as follows.

Code:

Sub

Sample()Dim

AAs Integer

A = InStrRev(" I am a Good Boy", " ")End Sub

Step 6: Now display the value stored in variable A using the msgbox function.

Code:

Sub

Sample()Dim

AAs Integer

A = InStrRev(" I am a Good Boy", " ") MsgBox AEnd Sub

Step 7: Let us execute the above code to get the following result.

We get the result as 13 because we did not provide the start position to the function so it automatically calculated the occurrence from the end and so the result. It is found that “ “ is on the 13th position of the string when we search it from the end.

Example #2 – VBA InStrRevIn the above example, we did not provide any start position to the string. Let us provide this time in this example. Let us find out from the second position where does the “ “ occurs in the string.

Step 1: Insert a new module from that option as shown below.

Step 2: Let us again define a subprocedure for our second example.

Sub

Sample1()End Sub

Step 3: Declare another integer variable for the example.

Code:

Sub

Sample1()Dim

AAs Integer

End Sub

Step 4: Now in Variable A let us find the occurrence of the “ “ from the second position using the INSTRREV function as follows.

Code:

Sub

Sample1()Dim

AAs Integer

A = InStrRev(" I am a Good Boy", " ", 2)End Sub

Step 5: Now use msgbox function to display the value stored in A.

Code:

Sub

Sample1()Dim

AAs Integer

A = InStrRev(" I am a Good Boy", " ", 2) MsgBox AEnd Sub

Step 6: Now run the above code to find out the below result as shown below,

We get 1 as a result as we count 2 we get I and after one position we get the occurrence of “ “.

Example #3 – VBA InStrRevIn this example let us use the compare methods. We have a string “ India is the Best” and let us find the string “E” using both text and binary compare methods.

Step 1: In the same module 1, write another subprocedure for example 3.

Code:

Sub

Sample2()End Sub

Code:

Sub

Sample2()Dim

A, BAs Integer

End Sub

Step 3: In variable A let us use the INSTRREV function with the text comparison as follows.

Code:

Sub

Sample2()Dim

A, BAs Integer

A = InStrRev("India is the Best", "E", , vbTextCompare)End Sub

Step 4: Now display the value stored in A using the msgbox function.

Code:

Sub

Sample2()Dim

A, BAs Integer

A = InStrRev("India is the Best", "E", , vbTextCompare) MsgBox AEnd Sub

Step 5: In variable B let’s use the binary comparison for the same string as follows.

Code:

Sub

Sample2()Dim

A, BAs Integer

A = InStrRev("India is the Best", "E", , vbTextCompare) MsgBox A B = InStrRev("India is the Best", "E", , vbBinaryCompare) MsgBox BEnd Sub

Step 6: Execute the above code to find the first result stored in variable A which is as follows.

Step 7: Press OK to see the result stored in variable B.

We get 0 as the result for binary compare because in our string “e” is present not “E”. In binary values both of these are different. So if a value is not found in the string we get a result as 0.

Things to Remember

The value returned by this function is numeric.

If the substring is not found the value returned is 0.

Start position is optional. If it is not provided, by default function search the occurrence from the end of the string.

The comparison methods are also optional.

Recommended ArticlesThis is a guide to VBA InStrRev. Here we discuss how to use Excel VBA InStrRev along with practical examples and downloadable excel template. You can also go through our other suggested articles –

You're reading __How To Use Excel Vba Instrrev With Examples?__

Update the detailed information about **How To Use Excel Vba Instrrev With Examples?** on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!