Land Of Oorang

VBA has a really useful function called InStrRev that finds the last occurrence of a sub-string in a string. To give an example InStrRev("C:\Test\Bar","\") would return 7 which is the position of the rightmost (or last) backslash. This is a very handy function to have around when you need to perform certain text parsing tasks, such as extracting a file name from a path etc.

Unfortunately, while Excel has FIND and SEARCH functions that work like InStr, there is no Excel equivialent to InStrRev. A lot of people simply use a UDF (user defined fuction) to just call the InStrRev in VBA, but there is a way to do it with just Excel formulas. Here is how:

=FIND(CHAR(26,),SUBSTITUTE("A1","MySubString",CHAR(26),(LEN(A1)-LEN(SUBSTITUTE(A1,"MySubString","")))/LEN("MySubString")))

How It Works:

The FIND and SEARCH functions can only find the first occurance of a substring. So in order to find the last occurance of a substring you must first make that substring unique within the string so it (and only it) will be found. The SUBSTITUTE function will replace a specific occurance of a substring with something unique. But you have to be able to specify the occurance number. We want the last occurance so all we need is a straight count of how many times the specified substring occurs in a string. Now to put it all together:

1.) Assume A1 has the value "C:\Test\Foo"
2.) Find the occurances of the specified substring within the string:
=FIND(CHAR(26,),SUBSTITUTE("A1","\",CHAR(26),LEN(A1)-LEN(SUBSTITUTE(A1,"","")))/LEN("\")))
=FIND(CHAR(26,),SUBSTITUTE("A1","\",CHAR(26),(11-9)/1))
=FIND(CHAR(26,),SUBSTITUTE("A1","\",CHAR(26),2))
4.) Replace the last occurance of the substring with a special char unlikely to occur in a string (I used the substitute character, it seemed fitting):
=FIND(" ","C:\Test Foo")
= 8