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

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Then in E1 I have:

Please Login or Register  to view this content.

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.

If there are no spaces in your folder names, this works:

=LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, "\", REPT(" ", 100)), 300))," ", "\"), FIND("\", SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, "\", REPT(" ", 100)), 300))," ", "\"))-1)

If there are spaces in your pathnames, a little uglier:

=SUBSTITUTE(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ","^"), "\", REPT(" ", 100)), 300))," ", "\"), FIND("\", SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ","^"), "\", REPT(" ", 100)), 300))," ", "\"))-1),"^"," ")