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

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

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

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

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

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