I entered this “8194680660384720” into 1 cell. it is converted into “power” in display. I have many cells with this situation.
I need the original string as shown. VALUE(text) and NUMBERVALUE can not do this.
Later I set the new entry cells to TEXT format, which works fine.
I still need the original string of older entries.
How to invert the cell with power display into number string displayed and save it as text?
You can show a formatted number for such an entry using the number format code “00000000000000000” or similar. However, there can’t be an assurance that the shown result will be the originally entered string of digits because actual numbers always are kept in RAM with 52 significant dyadic digits.
If you want to keep strings looking like numbers with many decimal digits, you need to set the number format of the respective cells to @ (“Text” meaning “Don’t try to recognize a number!”) in advance of entering/editing.
You can use shortcut key Ctrl + T (for text format) before entering such a large number. You need “Disable Autoformat” extension installed for that keyboard shortcut to work.
For the 8194680660384720 value you probably just need to widen the column, if it is too narrow the display string is switched to exponential and rounded according to the available width.
Apart from that, the maximum input value for numeric integer numbers preserving all digits is 9007199254740991, which is =2^53-1 with all mantissa bits of the IEEE 754 double precision binary floating point format used in internal representation. An input of 9007199254740992 already results in 9.00719925474099E+015 and there is no way to “convert” it back because the significand’s precision was exhausted.
To preserve all digits of an arbitrary length text string force the input to text cell content by preceding it with a ' apostrophe, like '9007199254740992, or pre-format cells with the @ Text number format before input, as Lupp mentioned.
4 Likes