filename without path?

20 views
Skip to first unread message

Jim Stoicheff

unread,
Dec 30, 2002, 2:14:47 PM12/30/02
to
I'd like to automatically insert the file name in a field
in a worksheet, but I don't want the entire path. Is this
possible?

John Wilson

unread,
Dec 30, 2002, 2:31:52 PM12/30/02
to
Jim,

This should work:

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

John

Jason Morin

unread,
Dec 30, 2002, 2:33:00 PM12/30/02
to
One way:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND
("]",CELL("filename",A1)))

Workbook must be saved for this to work.

HTH
Jason
Atlanta, GA

>.
>

David McRitchie

unread,
Dec 30, 2002, 2:52:22 PM12/30/02
to
Hi Jim and John,
You should not be using crippled form of CELL("filename") instead
of CELL("filename",A1) for more information see
Pathname in headings, footers, and cells
http://www.mvps.org/dmcritchie/excel/pathname.htm
which includes additional examples to obtain the filename,
sheetname, etc., but want to direct you to

The crippled form =cell("filename") indicates the last sheet update,
which can be useful if you want to see the sheetname that was last
updated, though this is not reliable because a recalculation will
change the value to the activesheet or the first sheet in a grouping
at the time of recalculation. A recalculation occurs when the workbook
is opened. See HELP, Cell worksheet function; for more information.

--
You can test this out by opening two workbooks and placing this
into a cell in one of them
=CELL("filename")
then change a cell in the other workbook, and return to the first.
I don't think that is the answer you want to see.

I believe I have pointed this out to you (John) several times in the past.

Jason's answer would be correct, as it does include the cell reference.

The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error). .
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John Wilson" <jwi...@optonline.net> wrote in message news:3E109F28...@optonline.net...

John Wilson

unread,
Dec 30, 2002, 3:25:57 PM12/30/02
to
David,

I don't really remember that you ever pointed this out to me in the past.
Regardless, you are correct and I should be more careful when I shoot
from the hip to answer a question.
Funny thing is that I found the correct code in google, couldn't figure
out why the "A1" cell reference was there, deleted it, tested it and
it "seemed" to work.

Thanks for keeping me honest.
Visited your link on this subject and learned a lot from it.
Next time I see this question again, I'll be sure to provide the link
to your site in lieu of exposing my ignorance.

John

Harlan Grove

unread,
Dec 30, 2002, 3:55:31 PM12/30/02
to
"John Wilson" wrote...

>This should work:
>
>=MID(CELL("filename"),FIND("[",CELL("filename"))+1,
>FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
..

Excel is braindead in some respects. In this case, Windows and presumably Mac OS
allow square brackets in filenames, so while the formula above may work most of
the time, it could break down in unusual (but NOT pathological) situations.
Better to search for the rightmost backslash (in Windows - is the Mac OS folder
separator still colon or has it become slash?).

In order to avoid a single monstrous formula, it's possible to use defined names
for this. For instance,

__UGH referring to =CELL("Filename")

__ASN referring to
=RIGHT(__UGH,MATCH("]",MID(__UGH,LEN(__UGH)+1
-ROW(INDIRECT("1:"&LEN(__UGH))),1),0)-1)

WBDN referring to
=LEFT(__UGH,LEN(__UGH)-MATCH("\",MID(__UGH,LEN(__UGH)+1
-ROW(INDIRECT("1:"&LEN(__UGH))),1),0))

WBBN referring to
=MID(__UGH,LEN(WBDN)+3,LEN(__UGH)-LEN(WBDN)-LEN(__ASN)-3)

and for the heck of it

WSN referring to
=MID(CELL("Filename",INDIRECT("A1")),LEN(WBDN)+LEN(WBBN)+4,255)

With this infrastructure, WBDN gives the workbook's directory name, WBBN gives
the workbook's base filename, and WSN gives the worksheet name for the worksheet
containing the formula evaluating itself (so =WSN in Sheet1!A1 gives Sheet1
while =WSN in 'Foo Bar'!X99 gives Foo Bar).

--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.

Harlan Grove

unread,
Dec 30, 2002, 4:04:49 PM12/30/02
to
Just saw David McRitchie's post, so revise the first defined names to

__UGH referring to =CELL("Filename",INDIRECT("A1"))

Why the INDIRECT? *Cut* and paste onto cell A1, delete row 1 or column A, and a
bare A1 or $A$1 would become #REF!. INDIRECT prevents that.

David McRitchie

unread,
Dec 30, 2002, 4:08:39 PM12/30/02
to
Hi John,
It usually takes a couple of times to really learn something
correctly -- it believe it was David Hager or possibly Alan Beban
that got me straight on this one to include the reference cell --
couldn't find where I got corrected
so may have been in email and it finally sank in during Aug 1999.
I think I ignored originally being told because I couldn't see a
difference for lack of a proper decriptive example or laziness on
my part.

. ---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"John Wilson" <jwi...@optonline.net> wrote in message news:3E10ABD5...@optonline.net...


> Thanks for keeping me honest.
> Visited your link on this subject and learned a lot from it.
> Next time I see this question again, I'll be sure to provide the link
> to your site in lieu of exposing my ignorance.
>

Norman Harker

unread,
Dec 30, 2002, 4:44:40 PM12/30/02
to
Hi David!

Re: Not using second argument in:

CELL("filename",A1)

You probably ignored requirement at first because the reason why was not
explained. It's not obvious until you hit a problem with leaving out the
second argument. That's one function of referring people to site's such as
yours for "further and better particulars." But I think a tad of
explanation, if short, takes the poster further.

Regards

--
Norman Harker
Sydney, Australia.
Massive free firework display tonight!
If you missed Christmas there's another on 7 January!
Happy New Year
(Whenever you celebrate it and whatever number you give it)
njha...@optusnet.com.au


Jason Morin

unread,
Dec 30, 2002, 5:08:09 PM12/30/02
to
I left out the reference A1 once and Chip Pearson scolded
me, but gave me an explanation of what can happen if you
exclude it...FYI.

http://makeashorterlink.com/?M42B36AE2

Jason

>-----Original Message-----
>Hi John,
>It usually takes a couple of times to really learn
something
>correctly -- it believe it was David Hager or possibly
Alan Beban
>that got me straight on this one to include the reference
cell --
>couldn't find where I got corrected
>so may have been in email and it finally sank in during
Aug 1999.
>I think I ignored originally being told because I
couldn't see a
>difference for lack of a proper decriptive example or
laziness on
>my part.

>.. ---


>HTH,
>David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
>My Excel Macros:
http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
>
>"John Wilson" <jwi...@optonline.net> wrote in message
news:3E10ABD5...@optonline.net...
>> Thanks for keeping me honest.
>> Visited your link on this subject and learned a lot
from it.
>> Next time I see this question again, I'll be sure to
provide the link
>> to your site in lieu of exposing my ignorance.
>>
>> David McRitchie wrote:
>> > You should not be using crippled form of CELL
("filename") instead
>> > of CELL("filename",A1) for more information see
>> > Pathname in headings, footers, and cells
>> > http://www.mvps.org/dmcritchie/excel/pathname.htm
>
>

>.
>

J.E. McGimpsey

unread,
Dec 30, 2002, 5:28:34 PM12/30/02
to
In article <7p2Q9.2906$15....@www.newsranger.com>, Harlan Grove
<hrl...@aol.com> wrote:

> Excel is braindead in some respects. In this case, Windows and
> presumably Mac OS allow square brackets in filenames

Yes, MacOS allows square brackets as well as forward and backslashes

> , so while the
> formula above may work most of the time, it could break down in
> unusual (but NOT pathological) situations. Better to search for the
> rightmost backslash (in Windows - is the Mac OS folder separator
> still colon or has it become slash?).

MacOS uses colons, but the Darwin/FreeBSD side uses slashes.

Jim Stoicheff

unread,
Dec 30, 2002, 7:36:18 PM12/30/02
to
Thanks, Jason, but this inserts the worksheet name, not
the filename.
>.
>

David McRitchie

unread,
Dec 30, 2002, 8:38:18 PM12/30/02
to
for the filename i.e. 1996FEDT.XLS

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

see http://www.mvps.org/dmcritchie/excel/pathname.htm for
additional items you can extract from CELL("filename",A1)


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jim Stoicheff" <jims...@hotmail.com> wrote in message news:043501c2b064$a21ad1c0$d3f82ecf@TK2MSFTNGXA10...

Reply all
Reply to author
Forward
0 new messages