Entering a filename as a variable into cell similar as for footer

[Doug]

Would like to have a cell value the the name of the excell file I am creating.
Similar to inserting the file name into the header or footer.

[Miguel Zapico]

You can use the function CELL, with the "filename" argument:
=CELL("filename")
This returns the full path. In case you just want the name you may need to
use some text functions. In the case I tried, with Excel 2003, the file name
was enclosed in braquets, so this formula gave just the bare name:
=MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-1)

Hope this helps,
Miguel.

"Doug" wrote:

> Would like to have a cell value the the name of the excell file I am creating.
> Similar to inserting the file name into the header or footer.

[CaptainQuattro]

Would like to have a cell value the the name of the excell file I am creating.
Similar to inserting the file name into the header or footer.

The following formula will return the full path, filename and current sheet name:

=CELL("filename",A1)

Where it gets interesting is if you want to isolate the file name only. For example, =CELL("filename",A1) may return

D:\Jupiter\MyDocuments\[doobie.xls]Sheet1

If all you want is

doobie.xls

you need the following:


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

The portion of the formula in red returns the position of the first character of the actual filename.

The portion of the formula in blue returns the length of the actual filename.

[Peo Sjoblom]

Just for fun

=REPLACE(SUBSTITUTE(CELL("filename"),INFO("directory")&"[",""),FIND("]",SUBSTITUTE(CELL("filename"),INFO("directory")&"[","")),255,"")

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Miguel Zapico" <[email protected]> wrote in message
news:[email protected]...
> You can use the function CELL, with the "filename" argument:
> =CELL("filename")
> This returns the full path. In case you just want the name you may need
> to
> use some text functions. In the case I tried, with Excel 2003, the file
> name
> was enclosed in braquets, so this formula gave just the bare name:
> =MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-1)
>
> Hope this helps,
> Miguel.
>
> "Doug" wrote:
>
>> Would like to have a cell value the the name of the excell file I am
>> creating.
>> Similar to inserting the file name into the header or footer.