VBA String Functions - Part 7

INSTR Function (VBA)

In Microsoft Excel, the INSTR function 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] )
start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.

string is the string to search  within.

substring is the substring that you want to find.

VBA Function Example

The INSTR function can only be used in VBA code in Microsoft Excel. Here are some examples of what the INSTR function would return:

InStr(1, "President of India", "of") would return 11
InStr("President of India", "India") would return 14
InStr(5, "President of India", "e") would return 7

Dim iPosition As Integer 
iPosition = InStr(5, "President of India", "e")

In this example, the variable called iPosition would now contain the value 7.

INSTRREV Function (VBA)

In Microsoft Excel, 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 INSTRREV function is:

InStrRev ( string, substring [, start [ , compare] ] )
string is the string to search within.

substring is the string that you want to find.

start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position -1 which is the last character position.

Note

If string2 is not found within string_being_searched, the INSTRREV  function will return 0.
If string_being_searched is zero-length, the INSTRREV function will return 0.
If string_being_searched is null, the INSTRREV function will return null.

If start is null, the INSTRREV function will return #Error.

VBA Function Example

The INSTRREV function can only be used in VBA code in Microsoft Excel. Here are some examples of what the INSTRREV function would return:

InStrRev ("alphabet", "a") would return 5

InStrRev ("alphabet", "a", -1) would return 5

InStrRev ("alphabet", "a", 1) would return 1

InStrRev ("alphabet", "a", 2) would return 1

InStrRev ("alphabet", "a", 3) would return 1

InStrRev ("alphabet", "a", 4) would return 1

InStrRev ("alphabet", "a", 5) would return 5

InStrRev ("alphabet", "a", 6) would return 5

InStrRev ("alphabet", "a", 7) would return 5

InStrRev ("alphabet", "a", 8) would return 5

InStrRev ("alphabet", "a", 9) would return 0

Dim iPosition As Integer
iPosition = InStrRev ("alphabet", "a")

In this example, the variable called iPosition would now contain the value 5.