last specific character in string

[Mark Peereboom]

I am currently working in Excel with a lot of paths like:
T:\Program Files\Common Files\InstallShield\Engine\6
\Intel 32\

I need to find a way to determine the position of second
to last backslash. I could also easily remove the last
character of the string in which case I would have to
look for the last backslash in the string. I don't know
if this would make it easier. I know there is a way to do
it in Basic but I am not really in to that.

Is there a way to achieve this without using Basic?

 

[Ken Wright]

With your string in A1:-

=FIND("%",SUBSTITUTE(A1,"\","%",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1))

Assumes % will not be used in any of your paths - If not the case, then simply
substitute another character.

 

[Vasant Nanavati]

Hi Ken:

Since I'm really bored today, I devised a solution that does not involve the
use of a dummy character:

=LARGE(((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&LEN(A1
)))),2)

array-entered, of course. Probably *much* less inefficient that the
traditional approach, but what the heck <g>.

Regards,

Vasant.

 

[Ken Wright]

Bored!!!!!!! You need to explain that concept to me at the moment Nah,
I'm on vacation, enjoying Xmas telly (Sad I know), Xmas food, and good company
from friends and family, not anticipating going to back to work till Jan 5th.
Long live the hols - Won't be bored till I get back to work!!!!

Inefficient - Who cares?? It was probably fun getting there, and that's the only
reason I play with this at all, is because I get a buzz out of it - So I like it
anyway - Always nice to have a choice of options, as you never know when one is
going to crap out for whatever reason, and then you gotta go reinvent the wheel
to get round whatever the limitation was. And I'll bet you end up doing
something else based on that as well (So I'll tuck it away, and thank you very
much). ;->

 

[Harlan Grove]

With your string in A1:-

=FIND("%",SUBSTITUTE(A1,"\","%",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1))

Assumes % will not be used in any of your paths - If not the case, then simply
substitute another character.

...

Vertical bars and angle brackets would all be better characters than % because
none of them are allowed anywhere in any Windows pathnames.

 

[Harlan Grove]

Since I'm really bored today, I devised a solution that does not involve the
use of a dummy character:

=LARGE(((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\")*ROW(INDIRECT("1:"&LEN(A1
)))),2)

array-entered, of course. Probably *much* less inefficient that the
traditional approach, but what the heck <g>.

If you're bored, start playing around with regular expressions. See

http://www.google.com/[email protected]

Using the Subst udf in that article, the problem reduces to

=Subst(Pathname,"^.*\\([^\\]+)\\?$","$1")

 

[Vasant Nanavati]

If you're bored, start playing around with regular expressions.

One of these days, when I'm feeling especially ambitious ... <g>.

 

[Ken Wright]

Cheers Harlan - Something else I didn't know, but I do now!!