VBA Instr Command

The VBA inst()  (The instring command) 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.
The VBA Instrrev(The reverse instring Command) 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.
If you enjoyed this post and video and would like to comment or share improvements please send your comments Here.  
If you would like to say hello and buy me a cup of coffee Click here.
Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here
If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me

Comments are closed.