top of page

How to use "VBA instr" and "VBA InstrRev"?

vba instr intrrev

Strings is just another word for text and in order to write strings you need to use the " quotation signs. This part will go through some practical ways to work with substrings. A great syntax here is Instr that searches left to right and InstrRev that searches in reverse (right to left) in vba. We will starting working with the classic analyst phrase. "Please Fix Thx." and modify that one throughout the examples on the page. Let's start with adding the text parts in to three strings.

A = "Please" B = "Fix" C = "Thx."

Now Let's add them all into one string D for further modifications.

'Let's make sure these are added into one string D.
D = A & " " & B & " " & C

Instr Syntax

Instr function similar to the "find" syntax where you want to find a substring in a string. Instr is a case sensitive function and it returns the starting position for a substring.

The syntax functions are the following:

InStr([ start ], string1, string2, [ compare ])

'Fi from 'Fix' is being found at startingposition 8.
MsgBox (InStr(D, "Fi"))

'Asking my code to start looking from Characterposition 10 in "Please Fix Thx."

'This is returning 0 as the substring "Fi" is not found after starting position 8.
MsgBox (InStr(10, D, "Fi"))

InstrRev Syntax

InstrRev is great for when you need to search your word from right to left. Reverse searching with InstrRev function

The syntax functions are the following:

InstrRev(stringcheck, stringmatch, [ start, [ compare ]])

MsgBox (InStrRev(D, "T")) 'returns position 12.

Learn much more about more text modifications in my bigger article here:

Learn more about VBA here for all my posts:

Learn more about Python here for all my posts:

If you have found this article or website helpful. Please show your support by visiting the shop below.

81 views0 comments


bottom of page