| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to display percentage with 0 decimal point by Text function? | Excel Discussion (Misc queries) | |||
| When I wrap text, excel hides the text display | Excel Discussion (Misc queries) | |||
| WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
| Function to display day in text format | Excel Worksheet Functions | |||
| in excel how to display results of a function and text in the sam. | Excel Worksheet Functions | |||
![Elton Law[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
