Excel: Truncate Text at Last Slash

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.

Tags:

Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>