Copy and pasted numbers not acting like numbers

[mklapp]

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,

 

[Luke M]

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".

 

[Luke M]

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".