Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract data from a cell reference
Dear Helpers,
I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the information "0502" from it so that I can make a date from it? thanks in advance. Fanny |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract data from a cell reference
=mid(cell ref,3,4) ???
"Fanny" wrote: Dear Helpers, I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the information "0502" from it so that I can make a date from it? thanks in advance. Fanny |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract data from a cell reference
=mid(cell("filename",A3),4,4)
cell will get the filename inthe cell A3, then mid will extract starting at character 4 and extract 4 characters. To get a date format you probably want to use the date function =date(2007,text(mid(cell("filename",A3),4,2),"gene ral"),text(mid(cell("filename",A3),6,2),"general") ) "Fanny" wrote: Dear Helpers, I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the information "0502" from it so that I can make a date from it? thanks in advance. Fanny |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract data from a cell reference
Hi Joel,
Sorry, I did not specify that the cell reference is referred to another workbook that the filename just capture the path of the local workbook not the source cell reference's path. The story is that I need to compare the daily profit variance while the daily profit file is saved as PL0102.xls, PL0202.xls, PL0302.xls and so on. I use a workbook to link to each PL workbook and just replace the filename. Then I want make use the file name to make the date. thanks in advance. Fanny "Joel" wrote: =mid(cell("filename",A3),4,4) cell will get the filename inthe cell A3, then mid will extract starting at character 4 and extract 4 characters. To get a date format you probably want to use the date function =date(2007,text(mid(cell("filename",A3),4,2),"gene ral"),text(mid(cell("filename",A3),6,2),"general") ) "Fanny" wrote: Dear Helpers, I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the information "0502" from it so that I can make a date from it? thanks in advance. Fanny |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract data from a cell reference
All we need to do then is to find the right bracket '[' first to remove the
path from the file name. the date is three character after the [ bracket =mid(cell("filename",A3),find("[",cell("filename",A3)) + 3,4) =date(2007,text(mid(cell("filename",A3),find("[",cell("filename",A3)) + 3,2),"general"),text(mid(cell("filename",A3),6,2), "general")) "Fanny" wrote: Hi Joel, Sorry, I did not specify that the cell reference is referred to another workbook that the filename just capture the path of the local workbook not the source cell reference's path. The story is that I need to compare the daily profit variance while the daily profit file is saved as PL0102.xls, PL0202.xls, PL0302.xls and so on. I use a workbook to link to each PL workbook and just replace the filename. Then I want make use the file name to make the date. thanks in advance. Fanny "Joel" wrote: =mid(cell("filename",A3),4,4) cell will get the filename inthe cell A3, then mid will extract starting at character 4 and extract 4 characters. To get a date format you probably want to use the date function =date(2007,text(mid(cell("filename",A3),4,2),"gene ral"),text(mid(cell("filename",A3),6,2),"general") ) "Fanny" wrote: Dear Helpers, I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the information "0502" from it so that I can make a date from it? thanks in advance. Fanny |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract data from a cell reference
Best to provide exactly what is now in cell B2 (If formula please show;
As well as what is being actually displayed ((by formula if formula) - or if no formula just how the constant or value in B2 "Fanny" wrote in message : Dear Helpers, I have a cell reference as [PL0502.xls]Sheet1!$B$2. How can I extract the information "0502" from it so that I can make a date from it? thanks in advance. Fanny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Column Letter from Cell Reference in another Cell | Excel Discussion (Misc queries) | |||
HOW TO EXTRACT CELL REFERENCE AS TEXT FROM NAMED RANGE | Excel Worksheet Functions | |||
How do I extract data from every other cell in a colomn? | Excel Worksheet Functions | |||
how to extract data from a cell in a formula in another cell | Excel Worksheet Functions | |||
extract data from a realtime updated cell | Excel Discussion (Misc queries) |