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!
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.
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
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)
