The VBA InStr function returns the index position of the first occurrence of a given substring within the provided string. Even if more than one occurance is found, InStr will return only the index position of the first matched occurance.
Here is a simple example of finding the position of the word “Hello” in the “Hi Hello” string:
Debug.Print InStr("Hi Hello", "Hello") 'Result: 4
Use InStrRev to return the index position of the first occurrence looking from the right end of the string.
VBA InStr Syntax
The syntax for the InStr function in VBA is:
InStr( [start], string, substring, [compare] )
|start||Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.|
|string||The string to search within.|
|substring||The substring that you want to find.|
|compare||Optional. The type of comparison to perform when looking for the substring. This can be one of the following VBA Constants:
The InStr function will return 0 if substring is not found within string.
Strings character positions in VBA are indexed starting with 1. See examples below.
The InStr function can only be used in VBA code. Let’s look at some VBA InStr function examples:
InStr "Hello World!", "World" 'Result: 7 InStr "Wow, Wow, Wow", "Wow" 'Result: 1 InStr 1, "Hi There!", "There", vbBinaryCompare 'Result: 4 InStr 1, "Hi There!", "there", vbBinaryCompare 'Result: 0 InStr 1, "Hi There!", "there", vbTextCompare 'Result: 4