8

I've been trying to find the extension from a file structure, and for that I tough about using InstrRev(filename, ".") but instead of returning 4, it's returning 30, the same way as Instr does ...

Anyone know of a weird error that would cause excel to give out the same result to 2 opposite functions?

Kind regards, Daniel

Dumitru Daniel
  • 571
  • 4
  • 19
  • 2
    That's the way it works. It looks back from the end of the string and returns the location with respect to the original string. It doesn't reverse the string or give the reverse location, it just searches from the end backwards. See the doco for more info – Nick.Mc Dec 15 '16 at 08:53
  • @Dumitru Daniel see my answer below, let me know if it answers your question, also see how to find the extension name – Shai Rado Dec 15 '16 at 09:30
  • You should be using the [`Scripting.FileSystemObject` for this](http://stackoverflow.com/documentation/vba/990/scripting-filesystemobject/11488/retrieve-just-the-extension-from-a-file-name#t=201612151318131020854) instead of manually trying to parse it. – Comintern Dec 15 '16 at 13:20
  • The accepted answer works very well of course.. but just for reference in my case I was being especially stupid using the InStrRev with "Right" function! changed that of course to "Mid" function and it.. worked :) – Dav.id Dec 19 '18 at 16:59

2 Answers2

13

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
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • 4
    Great answer, however you should emphasize one thing: the result character position is always counted from the *beginning* of the string. This can be confusing for rookies. So even though instrrev looks for a match from the end of a string going towards the beginning, when it finds the match, it returns it's proper character position counted from the beginning and *not* the end. – vacip Dec 15 '16 at 09:53
  • 2
    Thanks for your explanation. You explained better than MSDN's documentation. – Necromancer Sep 04 '17 at 05:52
  • Another encouragement to use the [GetExtensionName](https://msdn.microsoft.com/en-us/library/x0fxha2a(v=vs.84).aspx) method of the [FileSystemObject](https://msdn.microsoft.com/de-de/library/z9ty6h50(v=vs.84).aspx). You'll have to add a _reference_ to the [Microsoft Scripting Runtime](https://stackoverflow.com/a/3236348/2712565), but it's far easier and safer. Consider this unlikely, but possible path: `C:\Users\Rados.hits\Desktop\SO2file` Using GetExtensionName, you can get the correct extension in 2 lines (1 for any subsequent calls). – GlennFromIowa Oct 11 '17 at 16:17
2

The function InStrRev finds the sting2 inside the string1 from the end is true, but it's return for the found position is from the beginning and not from the end.

Elias Daoud
  • 29
  • 3