VBA Instr Command

The instring instr() command is useful in determining if a word or combination of characters exist in a text string.  It returns 0 when it doesn’t find the string. If it does it returns the position of the start of the word or collection of characters.
Remember:

  1. It does so from left to right
  2. Use the instrrev() if you need right to left
  3. It must be an exact match – yes, it is case sensitive

Example:  The activecell contains the English language pangram:
“The quick brown fox jumps over the lazy dog”
a phrase that contains all of the letters of the alphabet. ,

instr(activecell.value, “fox”)


instr(activecell.value, “Fox”)

would return a value of 17.  The “f” in fox is the 17th character in our string. 

would return a value of 0 as the command is case sensitive.

If we arbitrarily modified our sentence and added a second fox: “the quick brown fox jumped over the lazy fox dog.” and wanted to find the last instance of our search word fox – reading right to left
instrrev(activecell.value, “fox”)

would return a value of 42.  The “f” in last fox reading right to left is the 42nd character in our string. 

Comments are closed.