Trim file name only

[ExcelQuestion]

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

 

[Guest]

Try:

=MID(R1,FIND("test.xls",R1,1),255)

HTH

 

[ExcelQuestion]

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

 

[Beege]

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]...

 

[Guest]

TRY:

=MID(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)

 

[ExcelQuestion]

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

 

[Guest]

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

 

[Guest]

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]

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.

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 th
file
name; the "#" could be replaced by another character which will no
occur in
the file path.

HTH