software

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 quot;test.xlsquot;?
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(quot; quot;,R1,FIND(quot;
quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))

Any help is greatly appreciated.

Thanks,
Ricky--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
View this thread: www.excelforum.com/showthread...hreadid=542626Try:

=MID(R1,FIND(quot;test.xlsquot;,R1,1),255)

HTH

quot;ExcelQuestionquot; wrote:

gt;
gt; In cell R1, I have the full file address C:\Documents and
gt; Settings\user\Desktop\test.xls
gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; I found this formula but it's written for Last Name, First Name MI (and
gt; couldn't convert it to this purpose.
gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt;
gt; Any help is greatly appreciated.
gt;
gt; Thanks,
gt; Ricky
gt;
gt;
gt; --
gt; ExcelQuestion
gt; ------------------------------------------------------------------------
gt; ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
gt; View this thread: www.excelforum.com/showthread...hreadid=542626
gt;
gt;


Hi, because the filename changes regularly, as well as the file folders'
location, I need to trim whatever the filename is after the quot;\quot; to
result in [filename.xls].
Thanks again,
RickyToppers Wrote:
gt; Try:
gt;
gt; =MID(R1,FIND(quot;test.xlsquot;,R1,1),255)
gt;
gt; HTH
gt;
gt; quot;ExcelQuestionquot; wrote:
gt;
gt; gt;
gt; gt; In cell R1, I have the full file address C:\Documents and
gt; gt; Settings\user\Desktop\test.xls
gt; gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; gt; I found this formula but it's written for Last Name, First Name MI
gt; (and
gt; gt; couldn't convert it to this purpose.
gt; gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt; gt;
gt; gt; Any help is greatly appreciated.
gt; gt;
gt; gt; Thanks,
gt; gt; Ricky
gt; gt;
gt; gt;
gt; gt; --
gt; gt; ExcelQuestion
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; ExcelQuestion's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=542626
gt; gt;
gt; gt;--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
View this thread: www.excelforum.com/showthread...hreadid=542626Ricky,

If you have morefunc.dll (google) there is a TEXTREVERSE() function that
would help:

=TEXTREVERSE(LEFT(TEXTREVERSE(E6),SEARCH(quot;\quot;,TEXTR EVERSE(E6),1)-1))

Beege

quot;ExcelQuestionquot; gt;
wrote in message
news:ExcelQuestion.27wxba_1147803611.7239@excelfor um-nospam.com...
gt;
gt; In cell R1, I have the full file address C:\Documents and
gt; Settings\user\Desktop\test.xls
gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; I found this formula but it's written for Last Name, First Name MI (and
gt; couldn't convert it to this purpose.
gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt;
gt; Any help is greatly appreciated.
gt;
gt; Thanks,
gt; Ricky
gt;
gt;
gt; --
gt; ExcelQuestion
gt; ------------------------------------------------------------------------
gt; ExcelQuestion's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34059
gt; View this thread: www.excelforum.com/showthread...hreadid=542626
gt;
TRY:

=MID(A1,FIND(quot;#quot;,SUBSTITUTE(A1,quot;\quot;,quot;#quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;\quot;,quot;quot;)))) 1,255)

quot;ExcelQuestionquot; wrote:

gt;
gt; In cell R1, I have the full file address C:\Documents and
gt; Settings\user\Desktop\test.xls
gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; I found this formula but it's written for Last Name, First Name MI (and
gt; couldn't convert it to this purpose.
gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt;
gt; Any help is greatly appreciated.
gt;
gt; Thanks,
gt; Ricky
gt;
gt;
gt; --
gt; ExcelQuestion
gt; ------------------------------------------------------------------------
gt; ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
gt; View this thread: www.excelforum.com/showthread...hreadid=542626
gt;
gt;


Thanks Toppers,
This formula is what I'm looking for. (No idea how the quot;#quot; 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,
RickyToppers Wrote:
gt; TRY:
gt;
gt; =MID(A1,FIND(quot;#quot;,SUBSTITUTE(A1,quot;\quot;,quot;#quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;\quot;,quot;quot;)))) 1,255)
gt;
gt; quot;ExcelQuestionquot; wrote:
gt;
gt; gt;
gt; gt; In cell R1, I have the full file address C:\Documents and
gt; gt; Settings\user\Desktop\test.xls
gt; gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; gt; I found this formula but it's written for Last Name, First Name MI
gt; (and
gt; gt; couldn't convert it to this purpose.
gt; gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt; gt;
gt; gt; Any help is greatly appreciated.
gt; gt;
gt; gt; Thanks,
gt; gt; Ricky
gt; gt;
gt; gt;
gt; gt; --
gt; gt; ExcelQuestion
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; ExcelQuestion's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=542626
gt; gt;
gt; gt;--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
View this thread: www.excelforum.com/showthread...hreadid=542626It 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(quot;\quot;,MID(A8,LEN(A8)-ROW(INDIRECT(quot;1:quot; amp; LEN(A8))),1),0))
which is an array entered formula (cntl-shift-enter.)
A link to the white paper is at:
www.emailoffice.com/excel/arrays-bobumlas.html
--
Kevin Vaughnquot;ExcelQuestionquot; wrote:

gt;
gt; Thanks Toppers,
gt; This formula is what I'm looking for. (No idea how the quot;#quot; fits into
gt; this equation to make it work though. Could you please explain for
gt; future reference? Thanks again.
gt;
gt; Begee,
gt; I checked out the Reversetext command. Good to know.
gt;
gt; Thanks,
gt; Ricky
gt;
gt;
gt; Toppers Wrote:
gt; gt; TRY:
gt; gt;
gt; gt; =MID(A1,FIND(quot;#quot;,SUBSTITUTE(A1,quot;\quot;,quot;#quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;\quot;,quot;quot;)))) 1,255)
gt; gt;
gt; gt; quot;ExcelQuestionquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; In cell R1, I have the full file address C:\Documents and
gt; gt; gt; Settings\user\Desktop\test.xls
gt; gt; gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; gt; gt; I found this formula but it's written for Last Name, First Name MI
gt; gt; (and
gt; gt; gt; couldn't convert it to this purpose.
gt; gt; gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt; gt; gt;
gt; gt; gt; Any help is greatly appreciated.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; Ricky
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; ExcelQuestion
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; ExcelQuestion's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=34059
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=542626
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; ExcelQuestion
gt; ------------------------------------------------------------------------
gt; ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
gt; View this thread: www.excelforum.com/showthread...hreadid=542626
gt;
gt;

Ricky,
The last quot;\quot; in the file path is substituted by the quot;#quot; which
is then used by the FIND function to get the start position ( 1) of the file
name; the quot;#quot; could be replaced by another character which will not occur in
the file path.

HTH

quot;ExcelQuestionquot; wrote:

gt;
gt; Thanks Toppers,
gt; This formula is what I'm looking for. (No idea how the quot;#quot; fits into
gt; this equation to make it work though. Could you please explain for
gt; future reference? Thanks again.
gt;
gt; Begee,
gt; I checked out the Reversetext command. Good to know.
gt;
gt; Thanks,
gt; Ricky
gt;
gt;
gt; Toppers Wrote:
gt; gt; TRY:
gt; gt;
gt; gt; =MID(A1,FIND(quot;#quot;,SUBSTITUTE(A1,quot;\quot;,quot;#quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;\quot;,quot;quot;)))) 1,255)
gt; gt;
gt; gt; quot;ExcelQuestionquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; In cell R1, I have the full file address C:\Documents and
gt; gt; gt; Settings\user\Desktop\test.xls
gt; gt; gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; gt; gt; I found this formula but it's written for Last Name, First Name MI
gt; gt; (and
gt; gt; gt; couldn't convert it to this purpose.
gt; gt; gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt; gt; gt;
gt; gt; gt; Any help is greatly appreciated.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; Ricky
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; ExcelQuestion
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; ExcelQuestion's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=34059
gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=542626
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; ExcelQuestion
gt; ------------------------------------------------------------------------
gt; ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
gt; View this thread: www.excelforum.com/showthread...hreadid=542626
gt;
gt;


Hi Toppers,
Good to know how the quot;#quot; 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:
gt; Ricky,
gt; The last quot;\quot; in the file path is substituted by the quot;#quot; which
gt; is then used by the FIND function to get the start position ( 1) of the
gt; file
gt; name; the quot;#quot; could be replaced by another character which will not
gt; occur in
gt; the file path.
gt;
gt; HTH
gt;
gt; quot;ExcelQuestionquot; wrote:
gt;
gt; gt;
gt; gt; Thanks Toppers,
gt; gt; This formula is what I'm looking for. (No idea how the quot;#quot; fits
gt; into
gt; gt; this equation to make it work though. Could you please explain for
gt; gt; future reference? Thanks again.
gt; gt;
gt; gt; Begee,
gt; gt; I checked out the Reversetext command. Good to know.
gt; gt;
gt; gt; Thanks,
gt; gt; Ricky
gt; gt;
gt; gt;
gt; gt; Toppers Wrote:
gt; gt; gt; TRY:
gt; gt; gt;
gt; gt; gt;
gt; =MID(A1,FIND(quot;#quot;,SUBSTITUTE(A1,quot;\quot;,quot;#quot;,LEN(A1)-LEN(SUBSTITUTE(A1,quot;\quot;,quot;quot;)))) 1,255)
gt; gt; gt;
gt; gt; gt; quot;ExcelQuestionquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; In cell R1, I have the full file address C:\Documents and
gt; gt; gt; gt; Settings\user\Desktop\test.xls
gt; gt; gt; gt; What's the formula to trim it to show just quot;test.xlsquot;?
gt; gt; gt; gt; I found this formula but it's written for Last Name, First Name
gt; MI
gt; gt; gt; (and
gt; gt; gt; gt; couldn't convert it to this purpose.
gt; gt; gt; gt; =TRIM(RIGHT(R1,LEN(R1)-IF(ISERROR(FIND(quot; quot;,R1,FIND(quot;
gt; gt; gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))),LEN(R1),FIND(quot; quot;,R1,FIND(quot;
gt; gt; gt; gt; quot;,R1,FIND(quot;\quot;,R1,1) 2))-1)))
gt; gt; gt; gt;
gt; gt; gt; gt; Any help is greatly appreciated.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; Ricky
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; ExcelQuestion
gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; ExcelQuestion's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=34059
gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=542626
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; ExcelQuestion
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; ExcelQuestion's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=542626
gt; gt;
gt; gt;--
ExcelQuestion
------------------------------------------------------------------------
ExcelQuestion's Profile: www.excelforum.com/member.php...oamp;userid=34059
View this thread: www.excelforum.com/showthread...hreadid=542626