VBA Faulty function InstrRev = Instr

The difference between Instr and InstrRev is the direction it is looking for a certain Sub-String. Instr is looking from the start to the end, while InstrRev is looking from the end of the string to the beginning. The result character position is always counted from the beginning of the string (from left to right).

I guess in your example, you have only one "." in your path, that's why the result is the same.

Let's consider a case you are looking for the "Clean" file name, so you are looking for the last \, then you will find the InStrRev function very useful (see code below for example).

For your case: if you want to find the extension name, let's say result is 31, then use the Mid function :

ExtensionStr = Mid(FullName, dotRes + 1, Len(FullName) - dotRes) ' < --- returns "xlsm"  

(dotRes = 31, ExtensionStr is a string representing the clean extension name)

---

Code Example

Sub Instr_InstrRev()

Dim instrRes As Variant
Dim instrRevRes As Variant
Dim dotRes As Variant
Dim ExtensionStr As String
Dim FullName    As String

FullName = "C:\Users\Radoshits\Desktop\SO2.xlsm"
instrRes = InStr(FullName, "\") ' <-- returns 3
instrRevRes = InStrRev(FullName, "\") ' <-- returns 27

' to answer your post how to find the "clean" extension name
dotRes = InStr(FullName, ".") ' <-- returns 31
ExtensionStr = Mid(FullName, dotRes + 1, Len(FullName) - dotRes) ' < --- returns "xlsm"

End Sub