last specific character in string

  • Thread starter Thread starter Mark Peereboom
  • Start date Start date
M

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?
 
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.
 
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.
 
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). ;->
 
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.
Click to expand...
...

Vertical bars and angle brackets would all be better characters than % because
none of them are allowed anywhere in any Windows pathnames.
 
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>.
Click to expand...

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")
 
Back
Top