Quote:
Originally Posted by Jo Freeman
Actually, I have just had an epiphany! You need to make sure the cell is a Text cell before entering a long number. You can't alter it afterwards.
|
Correct. But you
can alter the
text afterwards.
Quote:
Originally Posted by ArviLaanemets
when you change the format of cell(s) to cardinally different one, like number to text or other way around, the actual values entered before dont get new formats automatically. to change the format of those entries you have. a) edit the entry, i.e. select a cell and press F2, and then press Enter, or double-click on cell, and then press Enter
|
Ordinarily, that is correct.
However, if the "number" has more than 15 digits, Jo is correct: we must set the cell format to Text
before entering the data. Alternatively, prefix the "number" with an apostrophe (single-quote; the character ' ).
Otherwise, we have already lost precision -- the digits to the right of the first 15 digits. Those digits are irretrievable.
For
data entry (manually or opening a text file, including a CSV file), Excel interprets only the first 15 significant digits, replacing any digits to the right with zero, effectively truncating after the 15th digit.
(It is better to import, not open, text files. Then in the last menu, we can select a column that has long "numbers" to be treated as Text.)
Moreover, when the "number" with more than 15 digits is entered as text, we do not get any more precision if we reference that cell in an arithmetic expression. Again, Excel interprets only the first 15 significant digits. The same is true of the VALUE function.
-----
Jo, if you truly want a numeric value with more than 15 digits, bear in mind that Excel cannot represent integers greater than 2^53 (9007199254740992) accurately. That is most 16-digit numbers. But it can do so only when the value is the result of arithmetic; for example, =9007199254740990+2.
(Errata.... I should have written "cannot represent
all integers greater than 2^53". Certainly,
some integers greater than 2^53 can be represented exactly.)
Moreover, Excel
formats only the first 15 significant digits (rounded), replacing any digits to the right with zeros. That is why 2^53
appears to be 900719925474099
0, but in fact it is 900719925474099
2.