excel

You are currently browsing articles tagged excel.

Let’s assume you have a value in cell E2 that has several components separated by slashes, e.g. a file path. You may find yourself in the position that you need to truncate the value at the last slash, e.g. get the full path of the folder that your file resides in.

E
2 /path/to/folder/file

The following formula will do the job:

=LEFT(E2,SEARCH("%",SUBSTITUTE(E2,"/","%",LEN(E2)-LEN(SUBSTITUTE(E2,"/",))))-1)

This will return “/path/to/folder”.

Please note that for the formula to work “%” must not be present anywhere in your value. If your value contains “%” you should substitute that in the formula with another character that is not present. (Know your legal characters!)

Update: Works in LibreOffice Calc, too, and I presume OpenOffice Calc will be no different.