VBA String Functions – INSTR() Function

.

Instr() Function

This is a VBA function and not a Worksheet function (Excel formula). This function returns the Position of First Occurrence of a SubString in a String. SubString could be a letter, Word or Sentence etc. It means this function always returns an Integer value. As mentioned in the definition of this function, it always returns the position number of the very First occurrence. Once first occurrence found, search stops there and returns the position number. It does not go and check any other occurrence.

Syntax:

This is how Syntax will look like as soon as you type VBA.Instr in VB Editor

Instr - Syntax

VBA.Instr(<Start>, <String1>, <String2>, <CompareMethod>)

Parameters:

Start(Optional) :

This is the start position where you want to start the search of your sub-string to find its first occurrence in the main string. This is an optional argument. If omitted search starts from the first character of the main string.

String1(Mandatory) :

This is the main string in which the sub-string is to be searched. This is a mandatory argument.

String2(Mandatory) :

This is the sub-string which has to be searched in the main string to find its first occurrence. This is again a mandatory argument.

CompareMethod (Optional) :

This is a Numeric value ( 0 or 1) to indicate which method to use for comparison. 0 is for performing a binary comparison. 1 is for performing a textual comparison. The default method is 0 – vbBinaryCompare

Examples:

With the above theory you may not be more comfortable until you practice with the some example by running the VBA code. I have taken few possible cases with different parameters in the below examples. They will make you more comfortable in using this function 🙂

Example 1 : Omitting all the Optional Parameters

In the below Example, I have omitted all the optional parameter and passed the String1 and String2 which are the mandatory parameters in this function.

String1 : My Name is Vishwa Mitra vishwa mishra
String2 : vishwa

 
VBA.Instr function will look like below with the above 2 argument passed in it:
 
VBA.Instr(“My Name is Vishwa Mitra vishwa mishra”, “vishwa”)
Following picture shows the integer value which is returned by the above VBA statement (refer the below picture)
 
Instr-Example-1

Explanation:

In the above code all the optional parameters are omitted. Hence using all the default values for other parameters, this function has returned the first occurrence of sub-string vishwa in the main string. This function has returned the count as 12.
In the above example default Start number is passed as 1 and compare method is passed as 0 – BinaryMethod.

Example 2 : What is difference between Binary and Text comparison?

It is very simple. As the name suggests in binary comparison, system compares the Numeric unicode value of each character. But in Text Comparison, comparison is based on the reference of the current world definition.
For example: Upper case – V and Lower case – v. Both “V” and “v” has different unicode value but as a text both are same. This is the reason Below statement 1 returns the first occurrence as 1 while the statement 2 returns as 8 while both the statements are exactly same except the comparison method. Refer the below two examples:

Statement 1: Text Comparison

SubStringFoundAt = VBA.InStr(1, “Vishwa vishwa Mitra mishra”, “vishwa”, 1)
Instr - Statement 1 - Text Compare

Statement 2: Binary Comparison

SubStringFoundAt = VBA.InStr(1, “Vishwa vishwa Mitra mishra”, “vishwa”, 0)
Instr - Statement 1 - Binary Compare

With the above example difference between Binary Comparison and Text Comparison is clear. Though, rest all the parameters are same in both the statement but still result is different because comparison method is different. Reason is that in Binary Comparison Vishwa is not equal to vishwa but in text comparison they are equal.

Example 3 : Impact of Start number

In the below example I will show you how Start number has an impact on the integer returned by this function.


Function INSTR_EXAMPLE()
Dim myString As String
Dim SubStringFoundAt1 As Integer
Dim SubStringFoundAt2 As Integer
    SubStringFoundAt1 = VBA.InStr(1, "vishwa vishwa Mitra mishra", "vishwa", 0)
    SubStringFoundAt2 = VBA.InStr(2, "vishwa vishwa Mitra mishra", "vishwa", 0)
End Function

In the above code first statement returns the integer as 1 while the second statement returns as 8> as shown in the below picture:
Instr - Example - Start

Explanation:

Case 1 : In the first statement the Start number is set as 1 hence Instr() function starts the search of sub-string vishwa right from the first letter of the main string vishwa vishwa Mitra mishra and it finds the first occurrence at the first place itself hence it returned the integer as 1
Case 2 : In the second statement start number is passed as 2; it means the Instr() VBA function will start the search from the second letter of the string vishwa vishwa Mitra mishra. This is the reason in this case this function will find the first occurrence of sub-string vishwa at the 8th position and it returned the integer as 8

Special Cases in INSTR()Function

Following are special cases in this function:

Case 1 : Sub-String NOT Found

Zero (0) is returned by Instr(), if the Sub-String is not found in the main string from the start number for search provided in the parameter. In the given example though the string vishwa is found but not starting from the 5th letter (Start=5) hence this function will consider as Sub-String NOT found in the main string and will return Zero (0) as shown in the below picture.

Case 2 : Main String passed with Zero length

Zero (0) is returned by Instr() function, if main string is passed of Zero length. Refer the below picture

Case 3 : Sub-String passed with Zero length

In this case this function returns the Start Number passed. Refer the below picture:

Case 4 : Start > length of Main string AND Sub-String is NOT of Zero Length

In this case when Start number is greater than the length of the main string AND sub-string is NOT of Zero length, Zero is returned. Refer the below picture.


Function INSTR_EXAMPLE()
Dim myString As String
Dim Case1 As Integer
Dim Case2 As Integer
Dim Case3 As Integer
    Case1 = VBA.InStr(5, "vishwa Mitra mishra", "vishwa", 0)
    Case2 = VBA.InStr(1, "", "vishwa", 0)
    Case3 = VBA.InStr(10, "vishwa Mitra mishra", "", 0)
    Case4 = VBA.InStr(100, "vishwa Mitra mishra", "vishwa", 0)
End Function

Instr - Special -Cases

Explanation:

Case 1. It returns Zero because vishwa is NOT found in the string 5th letter (because Start = 5)
Case 2. Returns Zero because the length of the main string is Zero.
Case 3. In this case it returns the Start number passed in the function because the sub-string to be searched is of zero length.
Case 4. This returns Zero because the Start (100) is greater than the length of the main string (19) AND String2 is not of Zero length.

Have you got any question or doubt related to INSTR() Function?

Do comment of mail me. I try my best to clarify.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest