InStr() Function

Searching and Comparing Strings in Excel VBA

The basic point of InStr() in Excel is to find out if one string is contained within another. The return value is a Variant (Long) that returns the position of the substring within the main string. Under some conditions, this return value can be used as a Boolean value.

The general format is:

InStr(start_position, main_string, substring , comparison)

Start_position and comparison are optional, a more general form is:

InStr(main_string, substring)

The required items are main_string and substring.

Parameter Type Description
start Variant – Numerical Value OPTIONAL. Set where in the main string you want to start searching for the substring. If you use the comparison parameter, this field is required.
main_string String REQUIRED. This is the string you want to search
substring String REQUIRED. This is what you are searching for
comparison Numeric Constant OPTIONAL. A value of -1, 0, or 1 determines the type of comparison. -1 is default.

Constant Numeric Value
vbUseCompareOption -1
vbBinaryCompare 0
vbTextCompare 1

 

Example 1:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "Hello World"
    
     Dim subString As String
     subString = "Hello"
    
     If (InStr(myString, subString)) Then
         MsgBox "True"
     Else
         MsgBox "False"
     End If
 End Sub

The above code should return back:

truemsg.png

If we alter that code:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "Hello World"
    
     Dim subString As String
     subString = "Hello Human"
    
     If (InStr(myString, subString)) Then
         MsgBox "True"
     Else
         MsgBox "False"
     End If
 End Sub

The substring is no longer contained within the myString variable and we receive a false message box.

falsemsg

Example 2:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
     Dim subString As String
     subString = "Z"
    
     Dim SubStringPos As Integer
    
     SubStringPos = InStr(myString, subString)
     MsgBox SubStringPos
 End Sub

This sets the subStringPos variable equal to the position where subString is found within the myString variable. Since “Z” is the 26th letter, we get back 26 as our value.

26msg

Example 3:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
     Dim subString As String
     subString = "C"
    
     Dim SubStringPos As Integer
    
     SubStringPos = InStr(12, myString, subString)
     MsgBox SubStringPos
 End Sub

I have added the optional starting position parameter. I have set it to 12. The substring we are looking for is “C” and that occurs in the 3rd position. This will result in the InStr() function not finding “C” and it should return a value of 0.

omsg

Example 4:

Private Sub CommandButton1_Click()
     Dim myString As String
     myString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
     Dim subString As String
     subString = "H"
    
     Dim SubStringPos As Integer
     Dim SubStringPos2 As Integer
    
     SubStringPos = InStr(2, myString, subString, vbTextCompare)
     SubStringPos2 = InStr(2, myString, subString, 1)
    
     MsgBox SubStringPos
     MsgBox SubStringPos2
 End Sub

I have added the optional comparison parameter using both the constant variable and the constant variable equivalent value.

Constant Numeric Value
vbUseCompareOption -1
vbBinaryCompare 0
vbTextCompare 1

This will result in two message boxes, both showing the position of “H” in the myString variable.

8msg.png

 

Excel VBA Resources

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: