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\

Answer 1

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.