Extract a text string from between the 3rd last pair of backslashes (from a dir tree)?

[kabammi]

Hi all,

First of all, thanks to anyone who can help.
I sort of have this working, but it is *really* messy. I've hacked it together from a couple of useful posts from help forums online (the main one from here, but my modifications have made it really dirty.

I'm using a macro I found online to provide me with a directory tree. The directories are listed in column A, and I want to grab a string from those cells. Specifically, I want to grab the 3rd directory back from the end. e.g.

A1
c:\this\is\a\variable\tree\with\manyvariable\directory\names

should return "manyvariable"
in C1

The number of directories in the tree varies, so counting between pairs of backslashes from the left won't fly. The one thing that *is* fixed, is that I always need the 3rd last directory as a string.

I've got it going in a rather convoluted manner. Because the way I'm doing it, I can't put the answer in C1, and it requires 3 cells to do the calculation in stages.

In C1 I have:

=LEFT((SUBSTITUTE(A1,(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"\","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))),"")),LEN((SUBSTITUTE(A1,(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"\","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))),"")))-1)

which strips off the last directory, and the terminal rightmost character that remains (which happens to be a backslash).
i.e. c:\this\is\a\variable\tree\with\manyvariable\directory

Then in D1 I have:

=LEFT((SUBSTITUTE(C1,(RIGHT(C1,LEN(C1)-FIND("^^",SUBSTITUTE(C1,"\","^^",LEN(C1)-LEN(SUBSTITUTE(C1,"\","")))))),"")),LEN((SUBSTITUTE(C1,(RIGHT(C1,LEN(C1)-FIND("^^",SUBSTITUTE(C1,"\","^^",LEN(C1)-LEN(SUBSTITUTE(C1,"\","")))))),"")))-1)

which is basically a repetition of the last function.
i.e. c:\this\is\a\variable\tree\with\manyvariable

Then in E1 I have:

=RIGHT(D1,LEN(D1)-FIND("^^",SUBSTITUTE(D1,"\","^^",LEN(D1)-LEN(SUBSTITUTE(D1,"\","")))))

This returns "manyvariable". Finally.

But boy, it is messy. I tried nesting all the code, but Excel 2010 has a hissy fit.. it's too big I think.

Now I'm more than happy to use an excel VB macro to do this, because there must be a smarter way!

Any ideas would be greatly appreciated!

best regards.