Link to home
Start Free TrialLog in
Avatar of sierra810
sierra810Flag for United States of America

asked on

in Excel how do I grab any text after the right most (last) backslash in a filepath?

   I need an excel formula guru to help with this, I have 180k lines of data in a column and I want to grab just the filename which is always at the end of the path but the amount of slashes varies depending on the folder structure.
prj_101461\cs\fld\99 RECYCLE BIN\10k mix_Iss1.pdf
prj_101461\cs\fld\08 REPORTS\Insurance Log\HP\ Insurance Log\03-646211263815639_Iss3.XLSM
Avatar of Tom Farrar
Tom Farrar
Flag of United States of America image

Are you talking about getting the last "_"?  Not sure what you meant by dashes.  
Oh, sorry you said slashes and not dashes.  Does the name always follow an "_" underscore?
If so, any easy way is to take into PowerQuery and parse on last "-" on right.
For that matter you could use text to columns with "_" as the delimiter.
Avatar of sierra810


sorry, I want to grab only the text after the last "\" , there could be 5 or 15 "\" in the string but I want to grab the information after the righmost one which is the end of the line and put it in the Excel cell right next to it.

which in this example will be just the 10k mix_Iss1.pdf

EXAMPLE : prj_101461\cs\fld\99 RECYCLE BIN\10k mix_Iss1.pdf

and in this example which has more "\"  the part I want is just the 03-646211263815639_Iss3.XLSM

EXAMPLE: prj_101461\cs\fld\08 REPORTS\Insurance Log\HP\ Insurance Log\03-646211263815639_Iss3.XLSM                        
Okay, again you can use Power Query to parse on the last "\" in the string. 
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Example of how Power Query works..

Or with VBA. This assumes data is in column 'A' and results are needed in column 'B'.

Sub AfterLastBackslash()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strParts() As String

lngLastRow = Range("A1048576").End(xlUp).Row

For lngRow = 1 To lngLastRow
    strParts = Split(Cells(lngRow, "A"), "\")
    Cells(lngRow, "B") = strParts(UBound(strParts))
End Sub

Open in new window

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel