| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filename extract from Filepath Text String | Excel Discussion (Misc queries) | |||
| How do I extract a filename from a formula linked to another book | Excel Discussion (Misc queries) | |||
| filename search and extract into a cell | Excel Discussion (Misc queries) | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
| how to extract a filename? | Excel Worksheet Functions | |||
![Steve[_16_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
