MS Excel: InStr Function (VBA only)

In Excel, the InStr function returns the position of the first occurrence of a string in another string.

The syntax for the InStr function is:

InStr( [start], string_being_searched, string2, [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_being_searched is the string that will be searched.

string2 is the string to search for.

compare is optional. This is the type of comparison to perform. The valid choices are:

VBA Constant Value Explanation

vbUseCompareOption -1 Uses option compare

vbBinaryCompare 0 Binary comparison

vbTextCompare 1 Textual comparison

vbDatabaseCompare 2 Comparison based on your database

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

InStr(1, "Tech on the Net", "the") would return 9

InStr("Tech on the Net", "the") would return 9

InStr(10, "Tech on the Net", "t") would return 15

VBA Code

The InStr function can only be used in VBA code. For example:

Dim LPosition As Integer

LPosition = InStr(10, "Tech on the Net", "t")

In this example, the variable called LPosition would now contain the value 15.