I have a number of workbooks, each with 50+ worksheets replicating with a
server. One field / cell in the replicated sheets reports "Last reported:
11/26/06". I'd like to do a copy / paste special of this cell from each
sheet to another sheet, but only include the date.

I can do a workaround by copying the entire text to the other sheet, and
just doing an align right in that column so only the date shows up, but if
the date shows up as 9/25/06, it shows up in the copied cell as :9/25/06.

I could also just globally select all replicated sheets and do an
edit/replace "Last reported: " and replace it with nothing. I was just
wondering whether there was any copy/paste special formula that would strip
out the text and just copy the date, instead of what I'm doing right now.

TIA,

Bob

Bob,
Here's a formula you can use =RIGHT([Book3]Sheet1!A7,9). Enter it in
the cell you want the information in. You will need to change
[Book3]Shee1!A7 to the name of the workbook, sheet and cell you are
pulling the data from.
Using =RIGHT will give you the number of spaces you indicate; the
delimiter being the last number of the formula. For example if you
changed 9 to 10 the results would be :9/25/06
If possible you will want to have a consistant date format in the
replicated sheets i.e. 02/02/2006 or 02/02/06. This way you won't have
to change the delimiter when the date changes from 9/ to 10/.

hope it's not too wordy

David


Bob Smith wrote:
I have a number of workbooks, each with 50+ worksheets replicating with a
server. One field / cell in the replicated sheets reports "Last reported:
11/26/06". I'd like to do a copy / paste special of this cell from each
sheet to another sheet, but only include the date.

I can do a workaround by copying the entire text to the other sheet, and
just doing an align right in that column so only the date shows up, but if
the date shows up as 9/25/06, it shows up in the copied cell as :9/25/06.

I could also just globally select all replicated sheets and do an
edit/replace "Last reported: " and replace it with nothing. I was just
wondering whether there was any copy/paste special formula that would strip
out the text and just copy the date, instead of what I'm doing right now.

TIA,

Bob

Thanks for the reply. Sorry for the delay in replying, been out of the
office. Your advise is not too wordy and works perfectly.

Thanks for the help.

Bob
"Tsunami3169" wrote in message
oups.com...
Bob,
Here's a formula you can use =RIGHT([Book3]Sheet1!A7,9). Enter it in
the cell you want the information in. You will need to change
[Book3]Shee1!A7 to the name of the workbook, sheet and cell you are
pulling the data from.
Using =RIGHT will give you the number of spaces you indicate; the
delimiter being the last number of the formula. For example if you
changed 9 to 10 the results would be :9/25/06
If possible you will want to have a consistant date format in the
replicated sheets i.e. 02/02/2006 or 02/02/06. This way you won't have
to change the delimiter when the date changes from 9/ to 10/.

hope it's not too wordy

David


Bob Smith wrote:
I have a number of workbooks, each with 50+ worksheets replicating with a
server. One field / cell in the replicated sheets reports "Last reported:
11/26/06". I'd like to do a copy / paste special of this cell from each
sheet to another sheet, but only include the date.

I can do a workaround by copying the entire text to the other sheet, and
just doing an align right in that column so only the date shows up, but
if
the date shows up as 9/25/06, it shows up in the copied cell as :9/25/06.

I could also just globally select all replicated sheets and do an
edit/replace "Last reported: " and replace it with nothing. I was just
wondering whether there was any copy/paste special formula that would
strip
out the text and just copy the date, instead of what I'm doing right now.

TIA,

Bob