VBA Instr Function

The InStr function finds the first place in the string where a given substring appears and returns that location. InStr is a VBA function, not a worksheet function. It means that it is not helpful in the worksheet. If String2 is empty, the function will produce the value of the [Start] argument. If the substring weren’t in the main string, the InStr function would return 0.

Syntax

InStr([Start], String1, String2, [Compare])

1. [Start] – (optional argument) It is an integer value that tells the InStr function where to look at the start. For example, if you want the find to start at the beginning, you will enter 1. If you want it to start with the third character and go on from there, you use 3. If not given, the value 1 is used as the default.

2. String1: It is the main string (or parent string) that you want to search. If you want to know where x is in Excel, String 1 would be “Excel.”

3. String2: It is the substring that you want to find. For instance, if you want to find out where x is in Excel, String2 would be x.

4. [Compare] – (optional argument) You can choose one of these three options for the [compare] argument:

• vbBinaryCompare: It compares each character against each others. For example, if you search for “X” in “Excel,” it will return 1, but if you search for “x” in “Excel,” it will return 0 because “X” is written in all capital letters. Instead of vbBinaryCompare, you can also use 0.
• vbTextCompare: It compares two pieces of text. For example, if you search for “x” or “X” in Excel, it will always return 1. The case doesn’t matter in this argument. You can also replace vbTextCompare with 1.
• vbDatabaseCompare: It is only helpful with Microsoft Access. You can also replace vbDatabaseCompare with 2.

See More: Excel VBA InStr Function

vbBinaryCompare:

Sub VBA_Instr_vbBinaryCompare()

MsgBox InStr(1, "XL AUTOMATION", "X", vbBinaryCompare)

End Sub

vbTextCompare:

Sub VBA_Instr_vbTextCompare()

MsgBox InStr(1, "XL AUTOMATION", "x", vbTextCompare)

End Sub

Examples

Let’s find out where the letter “A” is in the word “XL Automation” by using the following code, such as:

You can now press F5 to run the above code, or you can run the code manually to see results like: