The InStr function locates a given substring inside the given string and returns the index of the first place of the substring. Unlike other worksheet functions, InStr can only be found in a VBA project. That rules out its use in the accompanying worksheet. The function will return the value of the [Start] parameter if String2 (the substring whose position you’re looking for) is empty. If the InStr function cannot locate the specified substring inside the supplied string, it will return 0. For instance, if you want to determine where the letter ‘x’ appears in the word “Excel,” you can use the Excel VBA InStr function, which will return the value 2.
Syntax of InStr Function
InStr( [Start], String1, String2, [Compare] )
[Start]- It is an integer value that tells the InStr function the starting place from which it should start looking. This value means the function where it should begin looking. For instance, if you wish to start the search from the beginning, you will enter the value 1 for the corresponding field. You can use the number 3 if you want it to begin with the third character and continue further if that is what you desire. If it is not specified, the number 1 is used as the default.
String1- The primary string, also known as the parent string, is where you wish to look for something within. If you were, for instance, searching for the location of the letter x in Excel, the first word in String 1 would be “Excel.”
String2- It is the portion of the string you can find and locate. If you want to get the location of x in Excel, for instance, you will use String2 as the value for x.
[Compare]- You have the option of specifying any one of the following three values for the [compare] argument:
vbBinaryCompare – This would be a comparison of each character individually. For instance, if you search for “x” in the programme “Excel,” it will return the value 2, but if you search for “X” in the programme “Excel,” it will return zero because “X” is written in upper case. Alternatively, you might use 0 in place of the vbBinaryCompare command. If the [Compare] option is left out, this will be useful as the default.
vbTextCompare – A textual comparison would be carried out using the vbTextCompare function. For instance, if you search for ‘x’ or ‘X’ in Excel, it will always return the same result, which is 2. This argument pays no attention to the capitalization of the words. You could alternatively use the value 1 in place of the vbTextCompare command.
vbDatabaseCompare – It is a tool that is useful with Microsoft Access databases. It makes use of the information that is stored in the database to make a comparison. Alternatively, you may use the value 2 in place of vbDatabaseCompare.
See More: VBA Functions
Examples of
1) Use the code below to locate the letter “L” in the word “XL AUTOMATION”.
Run the above code using F5 or manually to get the following output.
2) Use vbBinaryCompare to locate the letter “x” in “XL AUTOMATION.”
Run the above programme. The compare argument given to the vbBinaryCompare InStr function returns zero because the word “XL AUTOMATION” doesn’t have an lowercase”m” letter.
