Trim file name only

E

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
 
E

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
 
T

Toppers

TRY:

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

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
 
K

Kevin Vaughn

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
 
T

Toppers

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
 
E

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
Click to expand...
 
Top