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,”\”,””)))))