Function to display text
 

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,

Function to display text
 

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

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



"Elton Law" wrote:

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,

Function to display text
 

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" wrote:

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,

Function to display text
 

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

"Teethless mama" wrote:

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

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



"Elton Law" wrote:

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,

Function to display text
 

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,

Function to display text
 

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

"Rick Rothstein" wrote:

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,

Function to display text
 

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 (MVP - Excel)


"Elton Law" wrote in message
...
Hi Teethless Mama,
That was my mistake. Yours works.
The rest works as well. I have tested. Thanks all of you.

"Rick Rothstein" wrote:

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,

Function to display text
 

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,

Function to display text
 

You're Welcome!


"Elton Law" wrote:

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

"Rick Rothstein" wrote:

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,