I’m trying to enter a 20 digit number in MS Excel (2003 XP2/ WinXP).
When I enter:
12345678901234567890
Excel truncates it to:
1.23457E+19 or 12345678901234500000
I tried changing “Format Cells” to Text, Custom, Number, etc. No dice.
Any ideas?
If you want it to be treated as a number, I think you’re out of luck. Like most programs, Excel has an upper limit on the accuracy (number of digits) allowed and it isn’t 20. If you just want the entry 12345678901234567890 to display (and be treated like a text string, then you can enter '12345678901234567890. That is, precede it with a single quote. I’m not sure what the upper limit on the length of a string, but it’s at least 100 characters long.
Did you try to expand the column size so that your number fits?
Format your cell to text before you enter the number
OldGuy has it. A bit of Googling turned up this article, which explains things more fully and says that the limit on precision is about 15 digits.
um… I just did it, and just like I said format the cell(s) to text before you enter the number.
The limit of precision is in fact 15 digits. When I enter 12345678901234567890 into my Excel it becomes this:
12345678901234500000
It doesn’t even round accurately, how nice.
Yeah, you see, this friend of mine who be Prince of Nigeria want to send me his all money fast fast and I don’t want to lose US$$67890. I have to sends him excel sheet with my bank all details.
That did it! I was trying to format it to Text after entering the number. Thanks, kanicbird.
And to think that I was worried that you were tallying up my S.D. demerits.
Just curious.
Why do you need to enter a 20 digit number into a spread sheet?
The long sequence is meaningless in practical applications.
Even the mantissas of logarithms are not carried that far.
Or like memorizing the long digit sequence for “Pi.”
Why?
You don’t even have to do that. Just put an apostrophe before the number when you enter it and it will make it into text.
'12345678901234567890
Which was the very first response to the thread
:smack: For some strange reason, I totally missed that. Yeah, that works nicely. Thanks.
A colleague of mine was trying to inventory some SIM cards using Excel, and they have this 20 digit number printed at the back. She didn’t care about the numerical value of the number.
OldGuy, you must be really old! I remember the apostrophe from Lotus 1-2-3 for MS-DOS. I didn’t know this trick still works on Excel.