If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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, |
Thread Tools | |
Display Modes | |
|
|