| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trim file name only
In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
Try:
=MID(R1,FIND("test.xls",R1,1),255) HTH "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
Hi, because the filename changes regularly, as well as the file folders' location, I need to trim whatever the filename is after the "\" to result in [filename.xls]. Thanks again, Ricky Toppers Wrote: Try: =MID(R1,FIND("test.xls",R1,1),255) HTH "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
Ricky,
If you have morefunc.dll (google) there is a TEXTREVERSE() function that would help: =TEXTREVERSE(LEFT(TEXTREVERSE(E6),SEARCH("\",TEXTR EVERSE(E6),1)-1)) Beege "ExcelQuestion" wrote in message news:ExcelQuestion.27wxba_1147803611.7239@excelfor um-nospam.com... In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
TRY:
=MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255) "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
Thanks Toppers, This formula is what I'm looking for. (No idea how the "#" fits into this equation to make it work though. Could you please explain for future reference? Thanks again. Begee, I checked out the Reversetext command. Good to know. Thanks, Ricky Toppers Wrote: TRY: =MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255) "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
It appears that the formula proferred is basically the same as the one
resented in a white paper by Bob Umlas. Interestingly enough, I was trying to come up with its brother earlier (from memory, but alas, my memory failed me and I had to search for the paper before finding my mistakes.) The formula I came up with (after having my memory jogged is: =RIGHT(A8, MATCH("\",MID(A8,LEN(A8)-ROW(INDIRECT("1:" & LEN(A8))),1),0)) which is an array entered formula (cntl-shift-enter.) A link to the white paper is at: http://www.emailoffice.com/excel/arrays-bobumlas.html -- Kevin Vaughn "ExcelQuestion" wrote: Thanks Toppers, This formula is what I'm looking for. (No idea how the "#" fits into this equation to make it work though. Could you please explain for future reference? Thanks again. Begee, I checked out the Reversetext command. Good to know. Thanks, Ricky Toppers Wrote: TRY: =MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255) "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
Ricky,
The last "\" in the file path is substituted by the "#" which is then used by the FIND function to get the start position (+1) of the file name; the "#" could be replaced by another character which will not occur in the file path. HTH "ExcelQuestion" wrote: Thanks Toppers, This formula is what I'm looking for. (No idea how the "#" fits into this equation to make it work though. Could you please explain for future reference? Thanks again. Begee, I checked out the Reversetext command. Good to know. Thanks, Ricky Toppers Wrote: TRY: =MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255) "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim file name only
Hi Toppers, Good to know how the "#" works. Thanks for your help. Ricky Thanks also to Kevin for the array formula and additional information. I've made notes for future reference. Toppers Wrote: Ricky, The last "\" in the file path is substituted by the "#" which is then used by the FIND function to get the start position (+1) of the file name; the "#" could be replaced by another character which will not occur in the file path. HTH "ExcelQuestion" wrote: Thanks Toppers, This formula is what I'm looking for. (No idea how the "#" fits into this equation to make it work though. Could you please explain for future reference? Thanks again. Begee, I checked out the Reversetext command. Good to know. Thanks, Ricky Toppers Wrote: TRY: =MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255) "ExcelQuestion" wrote: In cell R1, I have the full file address C:\Documents and Settings\user\Desktop\test.xls What's the formula to trim it to show just "test.xls"? I found this formula but it's written for Last Name, First Name MI (and couldn't convert it to this purpose. =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))),LEN(R1),FIND(" ",R1,FIND(" ",R1,FIND("\",R1,1)+2))-1))) Any help is greatly appreciated. Thanks, Ricky -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 -- ExcelQuestion ------------------------------------------------------------------------ ExcelQuestion's Profile: http://www.excelforum.com/member.php...o&userid=34059 View this thread: http://www.excelforum.com/showthread...hreadid=542626 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel file lost | Excel Discussion (Misc queries) | |||
| Copy File Automatically on Opening It | Excel Discussion (Misc queries) | |||
| Pasword protected Excel file encrypted, how do I read this file? | Excel Discussion (Misc queries) | |||
| Links picking up values from an older version of linked file | Links and Linking in Excel | |||
| Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Linear Mode
