Remove text after last Backslah

Question

I have a bunch of file paths that look like this:

e:\Datasheet\Docs\images\1.2.840\1.2.840.113986

I'm looking for a way in Excel to trim off everything after the last backslash \.

I've tried =LEFT(A1,FIND(",",A1)-1) but all I get back is two numbers.

The expected outcome I'm looking for is: e:\Datasheet\Docs\images\1.2.840\

Will the number before you truncate it always end in a decimal? — BruceWayne, Aug 28 '15 at 19:53

score 2 · Accepted Answer · edited May 23 '17 at 11:58

2

Assuming that you only want to go four folders "deep" this will work (place in B1):

=LEFT(SUBSTITUTE(A1,"\",";",5),SEARCH(";",SUBSTITUTE(A1,"\",";",5))-1)

edit: Try this one too, it should work for everything else (no matter how many folders):

=LEFT(A1, FIND(CHAR(1), SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", ""))))-1)

(Thanks to @Jerry) for that one.

edited May 23 '17 at 11:58

Community

answered Aug 28 '15 at 19:55

BruceWayne

that worked perfectly. I used 8 instead of 5 to get to the backslash I was expecting. Thank you! – user1442336 Aug 28 '15 at 19:57
I updated with a more dynamic formula, which also works. If this is what you're looking for, would you kindly flag as the answer? (click the check mark on the left, thanks!) – BruceWayne Aug 28 '15 at 19:58
1

yea I noticed that I have folder paths that the 8 or 5 would't work for. so the dynamic method you posted was much better and did the job. – user1442336 Aug 28 '15 at 20:04

Excel Hero · Answer 2 · 2015-08-28T20:19:00.430

1

This is all you need:

=LEFT(A1,MAX((MID(A1,ROW(1:999),1)="\")*ROW(1:999)))

Confirm it with Control-Shift-Enter.

And here is a short formula that does not need to be array-entered:

=LEFT(A1,LOOKUP(2^15,FIND("\",A1,ROW(OFFSET(A1,,,LEN(A1))))))

edited Aug 28 '15 at 20:19

answered Aug 28 '15 at 20:01

Excel Hero

2 Answers2