Method using Excel to extract part string of delimited text

Excel really needs a string reverse or find last character or VB split() like function. In VB or VBA it's simple: 
 To use Excel to extract part (usually the file name) of a path (or other string of delimited text) without resorting to VBA use the following:

Mid(path, InStrRev(path, "\")+1, Len(path))

=MID(B96,SEARCH(CHAR(222),SUBSTITUTE(B96,"\",CHAR(222),LEN(B96)-LEN(SUBSTITUTE(B96,"\",""))))+1,255)

The SUBSTITUTE function replaces all or a nth instance of a specific character. In this case a file path is being analyzed for the number of "\" characters. This is obtained by substituting "\" with nothing and subtracting the length of the shortened path string from the full path string. This provides the number of the last backslash. The SEARCH function finds this last substitution and provides the beginning of the file name for the MID function. CHAR(222) is used as replacement character because it cannot be in the path. Any unprintable character will do.

Replace "B96" with the cell address containing your text.

As and example:

       96

B    C:\my files\spreadsheets\2011\april\bnx.xls

Analyzing from inside out

SUBSTITUTE(B96,"\",""))

results in C:my filesspreadsheets2011aprilbnx.xls

LEN(SUBSTITUTE(B96,"\",""))

results in 38

LEN(B96)-LEN(SUBSTITUTE(B96,"\",""))

results in 43 - 38 or 5, the number of the last backslash

SUBSTITUTE(B96,"\",CHAR(222),LEN(B96)-LEN(SUBSTITUTE(B96,"\","")))

results in C:\my files\spreadsheets\2011\april[222]bnx.xls

SEARCH(CHAR(222),SUBSTITUTE(B96,"\",CHAR(222),LEN(B96)-LEN(SUBSTITUTE(B96,"\",""))))+1

results in 36

and, finally, the entire nesting

=MID(B96,SEARCH(CHAR(222),SUBSTITUTE(B96,"\",CHAR(222),LEN(B96)-LEN(SUBSTITUTE(B96,"\",""))))+1,255)

gives you bnx.xls