MrExcel Publishing
Your One Stop for Excel Tips & Solutions

paste values as numbers not text


Posted by Kristen on August 16, 2000 7:25 PM

I was wondering if you could help.....

I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records.....

cheer,

kristen


Posted by Celia on August 23, 0100 1:34 AM

Kristen
Assuming the cell that you want to format is A1, try adding this line of code :-

Range("A1").Value=Range("A1").Value

Celia

Posted by Thomas Venn on August 24, 0100 1:41 PM

I am still not completely clear as to how you are arriving at your data. but, try doing this after you have ran your paste macro: highlight your column of text information, then go to Data->Text to Columns...-> and follow the wizard. On step 3, choose Date. (if date does not work, choose General and see if it works.) I think the problem with what you are doing might have something to do that in order for a Time to show up, Excel requires a date also.

Cheers,

Thomas


Posted by Thomas Venn on August 17, 0100 3:14 PM

Interesting delimma. Perhaps you can share more information such as the formatting of the time. I am not quite sure what (eg 35:12:11) means. Is it hours, minutes, seconds? if so, what is the date. For when I copy this (eg 35:12:11) to Excel, it give me a date also (01/01/1900 11:12:11 AM).

On way you might want to try getting around this problem is have your data (the results of your formula) seperated into different columns, then CONCATENATE it all back together in your new worksheet.

Cheers,

Thomas I was wondering if you could help..... I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records..... cheer, kristen

Posted by Celia on August 17, 0100 3:43 PM

I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records..... cheer, kristen


Kristen
Try formatting the receiving cells in the format you want (presumably h:mm:ss)- you can do this in your macro.
Celia

Posted by Celia on August 17, 0100 3:48 PM

Thomas
The cell that displays 35:12:11 is probably formatted as [h]:mm:ss.
Or could be as text.
Celia

Interesting delimma. Perhaps you can share more information such as the formatting of the time. I am not quite sure what (eg 35:12:11) means. Is it hours, minutes, seconds? if so, what is the date. For when I copy this (eg 35:12:11) to Excel, it give me a date also (01/01/1900 11:12:11 AM). On way you might want to try getting around this problem is have your data (the results of your formula) seperated into different columns, then CONCATENATE it all back together in your new worksheet. Cheers, Thomas : I was wondering if you could help..... : I have a macro which copies a column of cumulative times (eg 35:12:11) from one sheet and pastes to another as values (which is necessary as I don't want to paste the formula). However it pastes as text (eg 00:12:35) so I am unable to run calculations on those values. If I select the field, hit F2 and then hit enter it converts it to a number format (eg 0:12:35) - which is what I want but I can't do it manually as there are potentially thousands of records..... : cheer, : kristen

Posted by Kristen on August 29, 0100 4:06 PM

What do you know! The text to columns worked... never used it or even noticed it before!

Thanks!! I am still not completely clear as to how you are arriving at your data. but, try doing this after you have ran your paste macro: highlight your column of text information, then go to Data->Text to Columns...-> and follow the wizard. On step 3, choose Date. (if date does not work, choose General and see if it works.) I think the problem with what you are doing might have something to do that in order for a Time to show up, Excel requires a date also. Cheers, Thomas


Posted by Kristen on August 22, 0100 7:17 PM

yep the format is [h]:mm:ss.

Celia - I've tried setting the cell format in the macro but when I paste as values it ignores the cell format and paste's the cell contents as text while retaining the cell format. Thomas

Posted by Celia on August 22, 0100 11:49 PM

Kirsten
Post your code so I can check.
Celia