We help IT Professionals succeed at work.
Troubleshooting Question

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

22 Views
Last Modified: 2020-07-23
Hi,
   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.
Example:
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
Comment
Watch Question

Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Are you talking about getting the last "_"?  Not sure what you meant by dashes.  
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Oh, sorry you said slashes and not dashes.  Does the name always follow an "_" underscore?
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
If so, any easy way is to take into PowerQuery and parse on last "-" on right.
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
For that matter you could use text to columns with "_" as the delimiter.

Author

Commented:
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                        
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Okay, again you can use Power Query to parse on the last "\" in the string. 
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Example of how Power Query works..

EE-2.xlsx
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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))
Next
End Sub

Open in new window

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.