Formula to find the last “ \ ” in a filepath and r

Question

So I have an excel task that involves taking filepaths (C:\foo...) and getting just the path (that is, removing the actual file name from the path). I can't seem to get the SEARCH/FIND function to work since it is always finding the first "\" in the file path (after the drive designation) and only removes 3 or so characters.

Is there a formula that will allow me to trim after the last "\" in the filepath?

Thanks in advance for any help!

Answer 1

First of all, your question would be better off on superuser.com.

You can use LEFT, with FIND and SUBSTITUTE... and a couple others:

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

LEN(A1)-LEN(SUBSTITUTE(A1, "\", "") basically gives the number of \ in the string.

SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", "")) This part substitute the last \ by a character called CHAR(1).

Then, use FIND to get the position of this character and minus 1 to remove that found character's position, to be LEFT (both figuratively and literally) with the part you need.

If you need the last backslash, remove the -1.

Answer 2

Try this version

=LEFT(A1,LOOKUP(2^15,FIND("\",A1,ROW(INDIRECT("1:"&LEN(A1))))))

The FIND function returns an array of numbers including the positions of all the "\" characters in A1 - LOOKUP takes the last number from that array - i.e. the position of the last "\" and then LEFT just takes the relevant portion based on that number

Answer 3

Alternates:

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)),"")
=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))))

And to remove the ending \ if desired:

=SUBSTITUTE(A1,"\"&TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)),"")
=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99)))-1)