extract characters from filename

[Steve]

I need a formula which will extract the last 4 characters from a file
name (before the extension). I would then need to convert this to the
proper date format if possible. For example, if the file name is
09291005.xls, I would want to extract the "1005", and have that become
the date, "10/05/2008. Is it possible to format a cell so that it
shows the date in this way, after extracting the information from the
filename?

Thanks in advance for any assistance...

 

[Peo Sjoblom]

Assuming that this is US date format, assuming there will always be 2
character each for month and day


=--TEXT(MID(CELL("filename",A1),FIND("xls",CELL("filename",A1))-5,4)&"08","00\/00\/00")

format as date


btw, how do you know it is 2008

--


Regards,


Peo Sjoblom

 

[T. Valko]

Is it the file name of the actual file or is this a file name that is
already entered in a cell?

Try this for the actual file:

=--TEXT(RIGHT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-5),4),"00\/00")

Format as Date

Note the file must have been saved at least once for this to work

 

[Steve]

Thanks Peo.

I have the files stored in a directory according to the year they were
created / modified. I guess I will have to change the formula you
provided when the year changes...

 

[Peo Sjoblom]

You can use the directory name, what is the directory name for 2008?

--


Regards,


Peo Sjoblom

Thanks Peo.

I have the files stored in a directory according to the year they were
created / modified. I guess I will have to change the formula you
provided when the year changes...

 

[Peo Sjoblom]

If you just call the directories for the year like 2008 it would be a breeze

=--TEXT(MID(CELL("filename",A1),FIND("xls",CELL("filename",A1))-5,4)&MID(CELL("filename",A1),FIND("[",CELL("filename",A1))-5,4),"00\/00\/0000")

--


Regards,


Peo Sjoblom

 

[Steve]

Thanks Peo.

I have the files stored in a directory according to the year they were
created / modified. I guess I will have to change the formula you
provided when the year changes...

 

[Steve]

Sorry about the double post.
Works great in the directory called "2008". No need to edit the
formula now when the year changes. Thanks again...