The formula is to find the last "\" in the file path and delete everything after that

Question

So, I have an excel task which involves getting the files (C: \ foo ...) and getting only the path (i.e. removing the actual file name from the path). It seems I can’t get the SEARCH / FIND function to work, because it always finds the first "\" in the file path (after the drive designation) and deletes only 3 characters.

Is there a formula that allows me to trim after the last "\" in the file path?

Thanks in advance for your help!

Answer 1

First of all, your question will be better at superuser.com .

You can use LEFT , with FIND and SUBSTITUTE ... and a couple more 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 \ in the string.

SUBSTITUTE(A1, "\", CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1, "\", "")) This part will replace the last \ with a character named CHAR(1) .

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

If you need the last backslash, remove -1 .

Answer 2

Deputies:

 =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) 

Answer 3

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 this array, that is, the position of the last "\", and then LEFT simply takes the corresponding part based on that number