Capture the Workbook Name

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
 
Use this formula in any cell of your workbook. It will output the name of
the workbook you place it in. So, for example, if you place this in
2004-08.xls, it will output "2004-08":

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH(".xls",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
 
=MID(CELL("filename"),1+FIND("[",CELL("filename")),FIND(".",CELL("filename"),FIND("[",CELL("filename")))-FIND("[",CELL("filename"))-1)

the file must be saved at least once before this formula will work.
 
Just a warning...

Change each of the:
CELL("filename")
to
CELL("filename",A1)

Else you'll get the filename of the activeworkbook when excel calculated.
 
Hey David and Sloth,

Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is, unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?

TIA

-Minitman



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

the file must be saved at least once before this formula will work.

Minitman said:
Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
Click to expand...
Click to expand...
 
I'd use this.

=DATE(MID(CELL("filename"),FIND("-",CELL("filename"))-4,4),MID(CELL("filename"),FIND("-",CELL("filename"))+1,2),1)

The final "1" in the formula indicates day one. You can format the
result to show the date in any way desired.

- John
www.JohnMichl.com
 
Mintman,

Try to wrap the formula they sent you in the DATEVALUE() function:

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

HTH,

Conan




Minitman said:
Hey David and Sloth,

Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is,
unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?

TIA

-Minitman



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

the file must be saved at least once before this formula will work.

Minitman said:
Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls
or
1999-03.xls). I would like to capture the date portion of this
name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
Click to expand...
Click to expand...
Click to expand...
 
Hey Dave,

Thanks for the warning. It is appreciated.

Any chance you could help with the conversion problem. (eg. the
result of this formula looks like a date. But when put into a date
cell, the other cells that are linked to it are giving me the #VALUE!
error or "This is not a date!")

Any suggestions would be very helpful.

-Minitman
 
Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman




Mintman,

Try to wrap the formula they sent you in the DATEVALUE() function:

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

HTH,

Conan




Minitman said:
Hey David and Sloth,

Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is,
unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?

TIA

-Minitman



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

the file must be saved at least once before this formula will work.

:

Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls
or
1999-03.xls). I would like to capture the date portion of this
name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
Click to expand...
Click to expand...
Click to expand...
 
Hey John,

That does the trick.

Thank you. My thanks to all of you.

-Minitman
 
How about:

=--(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),
FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))
-FIND("[",CELL("filename",A1))-1)&"-01")

(all one cell)

And format it as a date.

This converts 2004-12 to December 1, 2004.
Is that the date you wanted?

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman

Mintman,

Try to wrap the formula they sent you in the DATEVALUE() function:

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

HTH,

Conan




Minitman said:
Hey David and Sloth,

Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is,
unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?

TIA

-Minitman



On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"

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

the file must be saved at least once before this formula will work.

:

Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls
or
1999-03.xls). I would like to capture the date portion of this
name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
Click to expand...
Click to expand...
Click to expand...
 
Hey Dave,

Thanks for the reply.

I could not get you code to work. But that's ok, John Michl's
solution solved the problem

-Minitman.




How about:

=--(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),
FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))
-FIND("[",CELL("filename",A1))-1)&"-01")

(all one cell)

And format it as a date.

This converts 2004-12 to December 1, 2004.
Is that the date you wanted?

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman

Mintman,

Try to wrap the formula they sent you in the DATEVALUE() function:

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

HTH,

Conan




Hey David and Sloth,

Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is,
unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?

TIA

-Minitman



On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"

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

the file must be saved at least once before this formula will work.

:

Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls
or
1999-03.xls). I would like to capture the date portion of this
name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
Click to expand...
Click to expand...
Click to expand...
 
Sorry, but it did work ok for me.
Hey Dave,

Thanks for the reply.

I could not get you code to work. But that's ok, John Michl's
solution solved the problem

-Minitman.

How about:

=--(MID(CELL("filename",A1),1+FIND("[",CELL("filename",A1)),
FIND(".",CELL("filename",A1),FIND("[",CELL("filename",A1)))
-FIND("[",CELL("filename",A1))-1)&"-01")

(all one cell)

And format it as a date.

This converts 2004-12 to December 1, 2004.
Is that the date you wanted?

Hey Conan,

Thanks for the reply.

Good idea, but it also returns the #VALUE! error.

-Minitman

Mintman,

Try to wrap the formula they sent you in the DATEVALUE() function:

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

HTH,

Conan




Hey David and Sloth,

Thanks guys. They both indeed return the text string for the file
name as you said. This maybe what I requested, it is,
unfortunately,
not seen as a date. Is there anyway to convert this string into a
fully functual date?

TIA

-Minitman



On Tue, 6 Dec 2005 11:56:02 -0800, "Sloth"

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

the file must be saved at least once before this formula will work.

:

Greetings,

I have a lot of workbooks with a date as a name (eg. 2004-08.xls
or
1999-03.xls). I would like to capture the date portion of this
name
with a formula in sheet 'Date' cell 'A4'.

Anyone have any ideas?

Your help is appreciated.

TIA

-Minitman
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linking Daily Workbooks 1
Reference a file by concatenating cell variables 13
How to copy & paste a worksheet but change 1 bit of the formula? 1
indirect function with r1c1 style 4
>> vlookup refering to a cell to get wookbook name 1
Formula To Use A Dynamic Named Ranges With VLOOKUP On Different Workbooks 6
Macro for summarizing multiple sheets 1
display date of today in cell X when number >0 in cell Y 2

Back
Top