String manipulation is a key skill in VBA and other programming languages. Today let’s focus on using string searching functions to extract SubStrings from other strings using the VBA InStr or the VBA InStrRev functions.

VBA InStr

The VBA InStr function finds the position of a substring matched within a certain string. Returns 0 if substring not found.

InStr is a very powerful and useful function when having to locate substrings withing another text string.

InStr Example

Let assume a simple text string “Hello There!”. Remember that VBA indexes strings starting at index 1:

'Hi There!
'123456789

Find substring “There”:

Debug.Print InStr("Hi There!", "There") 
'Result: 4

Find second occurance of character “e” (first “e” is on position 6):

Debug.Print InStr(7, "Hi There!", "There") 
'Result: 8

VBA InStrRev

The VBA InStrRev function finds the position of a substring matched within a certain string, starting from the end of the string. The position from the string is counted from the left like for the InStr function. Returns 0 if substring not found.

InStrRev Example

Let assume a simple text string with multiple occurances”Hi There! You There!”. Remember that VBA indexes strings starting at index 1:

Find the last occurance of the substring “There”:

Debug.Print InStrRev("Hi There! You There!", "There") 
'Result: 15

Find second last occurance of substring “There”:

Debug.Print InStr("Hi There! You There!", "There", 18) 
'Result: 4

Why in the second example this I provide 18 as the position to start from instead of say 14? That’s because looking from the back There starts at position 19. Look at the image below:

InStr Comparison types

There is some confusion with the Comparison Types when dealing with InStr and InStrRev functions. What’s the difference?

vbBinaryCompare

This option compares each character in a string match exactly as according to it’s binary value. What does this translate to? Letter case will be distinguished (small from capital letters). See example below:

Debug.Print InStr(1, "Hi There!", "There", vbBinaryCompare)
'Result: 4

Debug.Print InStr(1, "Hi There!", "there", vbBinaryCompare)
'Result: 0 (not found)

vbTextCompare

This option ignores letter case (small vs. capital letters):

Debug.Print InStr(1, "Hi There!", "There", vbTextCompare)
'Result: 4

Debug.Print InStr(1, "Hi There!", "there", vbTextCompare)
'Result: 4