Function to display text

[Elton Law]

Dear Expert,

I have a cell containing text as follows:

R:\Team A \Client Vals\Equity Derivatives\Daily\Valuations Temp\Fixings.xls

How can I display the text from second last of \ or last of the \ please ?
In this case, for second last of \ should be Valuations Temp\Fixings.xls
In this case, for last of \ should be Fixings.xls

Thanks in advance,

 

[Teethless mama]

Second last \
=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",5))+1,99)

Last \
=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",6))+1,99)

 

[Mike H]

Hi,

Lets say you string is in A2 and a1 contains the occurence so a 3 in A1
extracts everything after the 3rd instance of \

try this

=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,"\",CHAR(1),A1))+1,LEN(A2))


Mike

 

[Elton Law]

Friend,
Appreciate your reply.
But it does not work. It gaves #Value!
Mine is Excel 2000. Does it matter? Thanks again!

 

[Rick Rothstein]

Off the top of my head...

Second from last
================================
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"\"&TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT("
",99)),99)),""),"\",REPT("
",99)),99))&"\"&TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

Last
================================
=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

Check back into this thread from time-to-time as I'm sure someone will post
a much shorter formula than my "second from the last" one... the formula for
the "last" should pretty much be the tightest one you will find.

 

[Elton Law]

Hi Teethless Mama,
That was my mistake. Yours works.
The rest works as well. I have tested. Thanks all of you.

 

[Rick Rothstein]

The only thing you should be aware of with Teethless Mama's solution is that
his formulas only work for directory paths containing **exactly** 6
backslashes... the solutions I posted are more general and will work for
directory paths containing 2 **or more** backslashes..

 

[Rick Rothstein]

Second from last

================================
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"\"&TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT("
",99)),99)),""),"\",REPT("
",99)),99))&"\"&TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

Here is a simpler general formula for the "second from last" (based on the
formula that Teethless mama posted)...

=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1))+1,99)

 

[Teethless mama]

You're Welcome!

Hi Teethless Mama,
That was my mistake. Yours works.
The rest works as well. I have tested. Thanks all of you.