Stop Excel from changing large "numbers" (actually text values) to scientific notation.
Prevent Excel from changing large "numbers" (actually text values) to scientific notation.
Thanks for the interest and discussion on this topic. To help us with planning, could you please answer this brief survey?
Best, Urmi [MS XL]
i'm personally really sick of technology that tries to "outsmart" us. people who aren't aware of it often get confused, and those of us who ARE aware of it often don't need or don't use it.
it should be OFF by default, and can be turned on if we feel the need to dig deeper...
Scott McGee commented
I'm actually okay with MS trying to save us time. But you've got to allow us to set a default that bypasses or turns off the time saving idea. Kind of like getpivot. D'oh! Yeah, I've heard some in Redmond thing that's cool. But - you're the only ones that do! LOL
Best Comment EVER!
This so called "feature" causes me to lose data and grief! ugh!
Anonymous commented · June 13, 2018 19:50 · Flag as inappropriate
"Whenever you have a "cool, time-saving programming idea", and it involves side effects with user's data, punch yourself in the face and dump a bucket of cold water over your head. Then don't write the code."
Whenever you have a "cool, time-saving programming idea", and it involves side effects with user's data, punch yourself in the face and dump a bucket of cold water over your head. Then don't write the code.
well, if an Anonymous asks, it MUST be so...
Add this feature to disable scientific notation!
The survey above is the fix. Just take it and you'll be fine.
yeah, you know, if they don't want to make Excel work for us, maybe they could add another program to Office that will do what the rest of us want...just saying.
Hey it's been 3 years and there's no feature for this. The amount of times that it deletes info while working, even when converting the cells to text. How long until we can just disable Scientific notation. I have no purpose for it in my line of data entry yet I use a lot of number heavy text cells.
and after importing into Excel and saving, the data becomes corrupted, despite being seen as text in Excel...
For me, a download of a file in CSV format, where a field may store 8154E09, if SAVED with a .txt extension, opens in Excel through the import wizard and specifying CSV, with the text value and not scientific notation. I could see in Notepad that the extract kept the text intact before Excel could corrupt it.
that's a valid thought David. i mean, if somebody was ACTUALLY using Scientific Notation, don't you think they would actually type it out as 6.352E+13 , as opposed to typing out a long string that will ultimately be truncated?
it seems to me that Microsoft is merely taking the shortcut from actual coding by just taking any long number and truncating it...
also, i vote that we call it "S.cientE+17" from now on, since people will obviously be able to tell what that means...because who doesn't prefer truncated information to the actual data?
Wondering how many users actually PREFER numbers to be changed to scientific notation by default... ? I would guess the minority. One more thing: when you implement this change, if a value is too large to store as a number (> 64 bits or whatever) then please don't change the least significant digits to 0s -- store it as a text value. I know--that will make us lose the ability to do mathematical operations. That's OK; at least the data is still accurate.
This has happened to me about 5.00E+15 times and it's so annoying!
Vote # 1.269E+3 here
yeah, I realize that 1,248 doesn't sound like much, compared to how many people there are in the world, but not EVERYBODY uses Excel.
and if you want to pull MORE people to use it, why not make it useful, instead of useless?
Mark Heuckeroth commented
If you are uncautious and save a CSV file you just opened, Excel literally destroys your numeric data. How can this be possible in a flagship product?
Ed Hansberry commented
@exacerbated. Yup, definitely happens. It is now second nature for me to press the windows menu key (usually to the right of the space bar), and the SV.
That is Paste Special, Values.
What is happening is the cell is formatted text, you are copying from somewhere else, even another app, and it brings a number format with it, then excel trashes it.
If you paste as values, only the raw ASCII gets pasted in there, no formats, so the Text format in Excel persists.
@Ed H - that's good advice, but unfortunately sometimes even that isn't enough. I've seen Excel change the format to general when pasting.
Ed Hansberry commented
@Exacerbated - make sure the table column with the long digits is formatted in Excel as TEXT format. Editing/entering data will retain the text format and not convert to a number or lose precision.
I'd add that using Power Query doesn't eliminate the issue in all cases. You might accidentally click twice on a cell that has a 16 digit number in it, click out, and voila, it has become SN because Excel thought you were editing the contents of the cell. Or you may have a list of long numbers in something like notepad, and when you copy and paste them into Excel, they become SN.
The problem can be doubled or tripled when you find a way to change it back, and fail to notice that the 16th digit has been changed into a zero. Then someone else uses the file, and is confused as to why 1234567890123450 is not a valid serial number or account number or whatever it is supposed to be. Because the 2nd 6 became a 0.