Second from last
================================
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"\"&TRIM(RIGH T(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)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Off the top of my head...

Second from last
================================
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"\"&TRIM(RIGH T(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.

--
Rick (MVP - Excel)


"Elton Law" wrote in message
...
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,