Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and pasted numbers not acting like numbers
Simply put, I have copied a column of numbers from one program into an excel
spreadsheet. Most of the time this works just fine, but this time, in order to preserve text formatting in one of the columns, I set the destination cells to a text format, then pasted the numbers. They will not add and formulas referencing the cells evaluating to a non-formula string. If I set Text Format after the paste, this problem does not occur. No amount of format reset seems to restore the preformatted cells to a useful state. Manually keying in new numbers works, but cut and paste seems like, on the surface, such a useful tool. My question is not how to work around this. I have already done that. As a matter of curiousity, I would like to know what is going on here that these values become unusable. Thank you, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy and pasted numbers not acting like numbers
When you preset the format of the cells to text, XL then stores all the
information as text. Unfortunately, "numbers" that are stored as text lose much of their usability in functions, (just like ="Tom"*4 does not make mathematical sense) hence the reason XL will try to keep you from storing numbers as text. However, as you saw in the 2nd instance when you laoded information, and then converted to text, XL is smart enough to say "Hey, I already have this information as a number. I'm not going to change it to text" and so while it will appear to left-align (by default) as text would, it's still stored as a number. A trick you can use sometimes, is if you allow the little green error triangle to appear, is when you see numbers sotred as text, select all the cells with the active cell being one of your numbers. You can now click on the error dropdown and select "Convert to number". -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "mklapp" wrote: Simply put, I have copied a column of numbers from one program into an excel spreadsheet. Most of the time this works just fine, but this time, in order to preserve text formatting in one of the columns, I set the destination cells to a text format, then pasted the numbers. They will not add and formulas referencing the cells evaluating to a non-formula string. If I set Text Format after the paste, this problem does not occur. No amount of format reset seems to restore the preformatted cells to a useful state. Manually keying in new numbers works, but cut and paste seems like, on the surface, such a useful tool. My question is not how to work around this. I have already done that. As a matter of curiousity, I would like to know what is going on here that these values become unusable. Thank you, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbers acting like text | Excel Discussion (Misc queries) | |||
copy numbers from numbers+text cell | Excel Worksheet Functions | |||
Why do pivot tables not read numbers that are copied and pasted | Excel Discussion (Misc queries) | |||
Help: Pasted red numbers turn black | Charts and Charting in Excel | |||
How can I keep leading 0's in pasted numbers? | Excel Discussion (Misc queries) |