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

Hi Brian, thanks

Teşekkürler.

test

test1

test3

test4

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.

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.

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.

Well, you can use Long Path Tool for such issues.