Function to display text

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

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,
 
Second last \
=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",5))+1,99)

Last \
=MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",6))+1,99)
 
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
 
Friend,
Appreciate your reply.
But it does not work. It gaves #Value!
Mine is Excel 2000. Does it matter? Thanks again!
 
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.
 
Hi Teethless Mama,
That was my mistake. Yours works.
The rest works as well. I have tested. Thanks all of you.
 
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..
 
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))
Click to expand...

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