extract characters from filename
 

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

---
Steve

extract characters from filename
 

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("file name",A1))-5,4)&"08","00\/00\/00")

format as date


btw, how do you know it is 2008

--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
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...

---
Steve

extract characters from filename
 

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

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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...

---
Steve

extract characters from filename
 

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

On Oct 6, 6:45*pm, "Peo Sjoblom" wrote:
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("file name",A1))-5,4)&"08","00\/00\/00")

format as date

btw, how do you know it is 2008

--

Regards,

Peo Sjoblom

"Steve" wrote in message

...

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

---
Steve

extract characters from filename
 

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

--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
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

On Oct 6, 6:45 pm, "Peo Sjoblom" wrote:
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("file name",A1))-5,4)&"08","00\/00\/00")

format as date

btw, how do you know it is 2008

--

Regards,

Peo Sjoblom

"Steve" wrote in message

...

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

---
Steve

extract characters from filename
 

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

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

--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
You can use the directory name, what is the directory name for 2008?

--


Regards,


Peo Sjoblom

"Steve" wrote in message
...
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

On Oct 6, 6:45 pm, "Peo Sjoblom" wrote:
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("file name",A1))-5,4)&"08","00\/00\/00")

format as date

btw, how do you know it is 2008

--

Regards,

Peo Sjoblom

"Steve" wrote in message

...

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

---
Steve

extract characters from filename
 

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

On Oct 6, 6:45*pm, "Peo Sjoblom" wrote:
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("file name",A1))-5,4)&"08","00\/00\/00")

format as date

btw, how do you know it is 2008

--

Regards,

Peo Sjoblom

"Steve" wrote in message

...

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

---
Steve

extract characters from filename
 

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

---
Steve


On Oct 6, 7:36*pm, Steve wrote:
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

On Oct 6, 6:45*pm, "Peo Sjoblom" wrote:

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("file name",A1))-5,4)&"08","00\/00\/00")

format as date

btw, how do you know it is 2008

--

Regards,

Peo Sjoblom

"Steve" wrote in message

....

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

---
Steve