Function to extract remaining string after last backslash

Question

I need an Excel function that can extract a string after last \ from a path and if no \ found then take the whole string. For example:

D:\testing\rbc.xls                     output will be   rbc.xls
D:\home\testing\test1\script1.sql      output will be   script.sql
script 3.txt                           output will be   script 3.txt

Answer 1

1.Change all the "\" to spaces, the number of spaces is determined by the number of characters in the cell

2.Use the right function to extract the right of the string based on the number of characters in the cell.

3.Use the trim function to remove the spaces.

Your results will be.

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

As suggested, one way to do this without formulas or vba would be to use "Find/Replace". Hit Ctrl & "H" keys and do the following.

Find what *\ and replace with nothing

The VBA code for that would be

Sub ReplaceIt()
    Columns("A").Replace What:="*\", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
End Sub