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.

  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.