Formula to find the last " \ " in a filepath and remove all after that

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.