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
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
>
>
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
Originally Posted by Toppers
Ricky,
If you have morefunc.dll (google) there is a TEXTREVERSE() function that
would help:
=TEXTREVERSE(LEFT(TEXTREVERSE(E6),SEARCH("\",TEXTREVERSE(E6),1)-1))
Beege
"ExcelQuestion" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
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
>
>
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
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
>
>
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
>
>
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks