How to: Split path and filename in MS Excel, Google Docs or OpenOffice Calc

I did a code review along with Brad Williams yesterday and today we combined our results.  The problem was, our spreadsheet columns didn’t match.  He’d combined path and filename into one column and I’d split them out into two columns. We decided the split path and filename was useful.  So I had to come up with  a quick set of formula to create the two extra columns, splitting path from filename.

For a  short time, I thought it wouldn’t be possible, and that my formula foo had  finally failed me.  The difficulty is of course that the find()  doesn’t have an option for returning the position of anything but the first occurrence of the search text.  So, you don’t have anyway to know where to split the string.  Ahhh, but lo and behold the substitute function can be used to replace the nth occurrence of a character or string.  If you replace the right duplicated character with something unique, you can come use find() to determine the right place do do the split.

The method I used was based upon formulas found on Excel Ticker in an article written by Mourad Louha.  It took me several reads through to grok the concept of his method. (Actually I came back to the article hours later to figure out how the formula worked – long after hacking the formulas to do what I wanted). So, I thought I would boil it down for you here.  Also his formula was based upon an unusual layout in his spreadsheet.  What I’ve listed here simply assume a 3 column spreadsheet (Folder, File, Full Path) with a row for the title.  If your third column (c2) has the concatenated/full path, just paste the right formula into the first and second columns (a2 and b2) in the second row and you are done.

Here is  Mourad’s method boiled down into simple steps:

1. Determine how many slashes there are.  Subtract the length of the string, with slashes removed, from the original length of the string.

2. Replace the LAST occurrence of the slash with unique text we can find (we substitute $$$ but anything unique could be used)

3.  We now use find()  to get the position of the unique text and use that as the location to split the string.

 

Open Office Calc/Google Spreadsheet Filename Split Formulas

Linux Formula for splitting the path off a filepath:
= LEFT($C2;FIND(“$$$”;SUBSTITUTE($C2;”/”;”$$$”;LEN($C2) – LEN(SUBSTITUTE($C2;”/”;””))))-1)

Linux Formula for splitting a file name off a filepath:
=RIGHT($C2;LEN($C2)-FIND(“$$$”;SUBSTITUTE($C2;”/”;”$$$”;LEN($C2)-LEN(SUBSTITUTE($C2;”/”;””)))))

Windows Formula for splitting the path off a filepath:
= LEFT($C2;FIND(“$$$”;SUBSTITUTE($C2;”\”;”$$$”;LEN($C2) – LEN(SUBSTITUTE($C2;”\”;””))))-1)

Windows Formula for splitting a file name off a filepath:
=RIGHT($C2;LEN($C2)-FIND(“$$$”;SUBSTITUTE($C2;”\”;”$$$”;LEN($C2)-LEN(SUBSTITUTE($C2;”\”;””)))))

 

Microsoft Excel Filename Split Formulas

Microsoft Exceluses commas instead of semicolons.

Linux Formula for splitting the path off a filepath:
= LEFT($C2,FIND(“$$$”,SUBSTITUTE($C2,”/”,”$$$”,LEN($C2) – LEN(SUBSTITUTE($C2,”/”,””))))-1)

Linux Formula for splitting a file name off a filepath:
=RIGHT($C2,LEN($C2)-FIND(“$$$”,SUBSTITUTE($C2,”/”,”$$$”,LEN($C2)-LEN(SUBSTITUTE($C2,”/”,””)))))

Windows Formula for splitting the path off a filepath:
= LEFT($C2,FIND(“$$$”,SUBSTITUTE($C2,”\”,”$$$”,LEN($C2) – LEN(SUBSTITUTE($C2,”\”,””))))-1)

Windows Formula for splitting a file name off a filepath:
=RIGHT($C2,LEN($C2)-FIND(“$$$”,SUBSTITUTE($C2,”\”,”$$$”,LEN($C2)-LEN(SUBSTITUTE($C2,”\”,””)))))

10 thoughts on “How to: Split path and filename in MS Excel, Google Docs or OpenOffice Calc”

  1. I think quick set of formula to create the two extra columns, splitting path from filename is the good idea to split the path and file name in MS excel, Google docs or open office calculator. Thanks for giving us the idea given above.

  2. With the whole thing that

    seems to be developing within this particular area, your
    points of view happen to be

    very radical. Having said

    that, I am sorry, but I can not give credence to your

    whole theory, all be it exciting none the

    less. It seems to us that your comments

    are actually not completely justified and in actuality you are yourself not wholly certain of the argument.
    In any

    event I did take pleasure in reading through it.

  3. I wanted to put you a little bit of note so as to thank you so

    much over again about the breathtaking methods

    you have provided on this website. It has been unbelievably
    open-handed with you to convey publicly

    precisely what many of us could

    possibly have advertised as an electronic book to earn some money for themselves, most importantly considering the

    fact that you might well have done it in case you wanted.
    The smart ideas

    also acted to

    become easy way to fully grasp that some people have similar fervor the same as my own to realize much more in

    terms of this condition.

    I’m sure there are millions of more pleasant opportunities up front for individuals that find out your blog post.

Leave a 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>