Extract data from a cell reference

[Fanny]

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

 

[Rich]

=mid(cell ref,3,4) ???

 

[Joel]

=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 functio
=date(2007,text(mid(cell("filename",A3),4,2),"general"),text(mid(cell("filename",A3),6,2),"general"))

 

[Fanny]

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

=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),"general"),text(mid(cell("filename",A3),6,2),"general"))

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

 

[Joel]

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"))

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

=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),"general"),text(mid(cell("filename",A3),6,2),"general"))

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

 

[JMay]

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