9

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
braX
  • 11,506
  • 5
  • 20
  • 33
user664481
  • 2,141
  • 9
  • 25
  • 31
  • welcome at stackoverflow. please note that this isn't a free code-writing service. please share what have you tried so far and where do you get stuck. – Máté Juhász Dec 12 '15 at 10:07
  • 1
    Have you looked at function `InStrRev` which finds the last occurence of a substring in a main string. `Pos = InStrRev(PathFileName,"\")` gives the last "\". `FileName = Mis$(PathFileName,Pos+1)` extracts the characters after the slash. – Tony Dallimore Dec 12 '15 at 10:42
  • I suggest you read down the alphabetic lists of VBA functions and methods. In my early days I did this every month or so until, when I came to a new problem, I knew there was a function that did what I needed even if I did not remember the syntax. – Tony Dallimore Dec 12 '15 at 10:46
  • I'm voting to close this question as off-topic because this site does not exist to duplicate the documentation provided with programming languages. – Tony Dallimore Dec 12 '15 at 10:50
  • 1
    Possible duplicate of [Extract filename from path](http://stackoverflow.com/questions/5932909/extract-filename-from-path) – lori_m Dec 12 '15 at 21:58

1 Answers1

24

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.

enter image description here

Your results will be.

enter image description here

=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

enter image description here

The VBA code for that would be

Sub ReplaceIt()
    Columns("A").Replace What:="*\", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42