Find SubString in a String - (InStr & InStrRev) Function VBA

saurabhlakhanpal's picture

In Microsoft Excel, the InStr function finds out if a SubString is present in a String and returns the position of the first occurrence of a SubString in a string.

Syntax:
The syntax for the INSTR function is - InStr( [start], String, Substring, [compare] )

Parameters:
Start (Optional)
Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. The start index is 1-based.

String (Required)
String expression being searched.

SubString (Required)
String expression sought.

Compare (Optional)
Specifies the type of string comparison. If Compare is omitted, the Option Compare setting determines the type of comparison. It can be one of the following values
VBA Constant Value Explanation
vbUserCompareOption -1 Uses option compare
vbBinaryCompare 0 Binary comparison
vbTextCompare 1 Textual comparison
vbDatabaseCompare 2 Comparison based on your database

Example:
The InStr function can only be used in VBA code. Below are some examples of what the InStr function would return.
InStr(1, "Blog on excel function", "fun") would return 15
InStr("Blog on excel function", "fun") would return 15
InStr(1, "Blog on excel function", "c") would return 11
InStr(12, "Blog on excel function", "c") would return 18

If you want to search the String starting from the reverse the InStrRev function can be used.
The InStrRev function returns the position of the first occurrence of a string in another string, starting from the end of the string. This is similar to the InStr function which returns the position of the first occurrence, starting from the beginning of the string.

Syntax:
The syntax for the INSTR function is - InStrRev( String, Substring, [start], [compare] )

Parameters:
The parameters are same as in the InStr function.

Example:
InStrRev ("alphabet", "a") would return 5
InStrRev ("alphabet", "a", 1) would return 1
InStrRev ("alphabet", "a", 3) would return 1
InStrRev ("alphabet", "a", 6) would return 5
InStrRev ("alphabet", "a", 9) would return 0

So till this point, you know how to find the first and last occurrence.
Now, let's see how to find the nth occurrence.
Insert the below code in the VBA editor and change the value of "last" to the occurrence you want to find. In the below code I'm searching for the 2nd occurrence.

Sub FindnthOccurence ()

Dim P As Long
Dim reqP As Long
Dim i as Long
Dim last as Long

P = 0
last = 2 'Chnage this value to find the nth occurence
for i = 1 to last
P = InStr(P + 1, String, SubString, [Compare] )
reqP = P
Next i

End Sub

I think this has been informative and I thank you for viewing.

-Saurabh