Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 47
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 21
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 422
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Column Letter from Cell Reference in another Cell JKBEXCEL Excel Discussion (Misc queries) 2 December 29th 06 05:27 PM
HOW TO EXTRACT CELL REFERENCE AS TEXT FROM NAMED RANGE romelsb Excel Worksheet Functions 0 November 3rd 06 10:49 PM
How do I extract data from every other cell in a colomn? Spencer Christensen Excel Worksheet Functions 1 March 31st 06 08:12 AM
how to extract data from a cell in a formula in another cell vidhya Excel Worksheet Functions 1 October 17th 05 04:31 PM
extract data from a realtime updated cell ALVESM Excel Discussion (Misc queries) 4 March 21st 05 06:21 PM


All times are GMT +1. The time now is 03:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"