extract characters from filename

  • Thread starter Thread starter Steve
  • Start date Start date
S

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...
 
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
 
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
 
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...
 
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...
 
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
 
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...
 
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...
 
Back
Top