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

D

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

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

CaptainQuattro

Doug said:
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.
Click to expand...

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:

=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.
If all you want is -doobie.xls- you need the following:
 
P

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