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 very much for your votes. And to those who took the time to fill in the survey, thank you! This is a brief update to let y’all know that we’ve started work on this feature request.
- Urmi [Msft]
You know, things should just simply be there as entered. Whetehr hand entered, dragged and dropped, imported, whatever, simply recorded as entered.
Then I write a formula that references it and Excel reads it... and does whatever it can. If I use it as a number and Excel can't figure out how to use the character string as a number — AT THAT MOMENT AND FOR THAT USAGE ONLY — it simply gives an approriate error. And continues to leave the underlying character string, in General format if I didn't choose to apply some other, ALONE.
If it's the equivalent of "82happysanta9" and I try to add it to something, well, my bad. No errors simply because I had to format it as text to keep the whole thing. Simple, straightforward.
I might also point out a niche advantage one would have here: since all the entered characters would be present, I, or Excel, could easily (relatively easily) calculate correct significant figures, ALWAYS. It would be so easy, they could easily program a format symbol to add to any numerical format it could work with that would literally, not just display-wise, change a result to only keep the proper significant figures. (I guess a second symbol would have to be available to mark some entries as constants so they get used properly in that.)
With that, everyone could use their own silo's standards about significant figures and rounding to give the results they need.
It's ridiculous... they say Excel has engineers in mind and then one of the most basic engines in it whacks characters from start and end of entered data with no workable way to prevent it. "Su data es mi data," sayeth the Excel.
Lee Chao commented
Excel shall not automatically convert text with all numbers longer than it can handle to numeric format. It shall simply keep it as text. User shall get an error if performing arithmetic on those values. It is too often account identifiers are long numeric numbers. They are used as identifiers not used in calculations.
I'd love to see scientific notation go away or be disabled, whatever the consequences. I hope this is acted upon someday.
@Zak, I have never spoken to an actual person, face to face, and had them complain about their hard drive space after Windows formats their disk.
It doesn't matter in the real world.
what about when people complain that their 1TB drive can't hold their 900GB file?
i realize it doesn't have anything to do with Excel, i was just telling him not to expect accuracy from a company with a historical lack of it.
@Zak - This is next level nerdery that doesn't matter to people in the real world. People who understand what a MB is vs an MiB can use Excel or other tools as needed.
The main issue I have is the "normies" use Excel. And when Excel jacks up their data they think my software is jacking it up. You can't explain these things to these people. They don't care. I don't care.
yes, but now that SI is the standard, shouldn't MS update their code? surely one of the dozens of updates i get each week should cover that...
this is what i mean by "inaccurate".
Zak, respectfully, come on... Before the SI got involved, the standard understanding was base 2 values close to the thousand point, which is 1024, were used for "mega". This is because of how data is actually stored.
I suggest you learn about the history of computing, how values are saved, and why some legacy things are what they are. You'd be a bit less snarky. Here's some discussion from Oracle on the limitations of their number formats. https://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_datatypes002.htm
well, he said he just wanted it to be accurate. i'm saying, accuracy is not a big thing with them, in my experience.
@Zak, why does that matter?
need i say it again? you know Windows is set so that a megabyte is 1024 kilobytes.
google "megabyte" and "mebibyte" sometime and see what i mean...
Leigh McDonald commented
While I appreciate the explanations of why this is more difficult on the back end than it may appear to users, my complaint really boils down to the fact that the text setting description literally says, verbatim: "Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered." THIS IS A LIE.
As much as I think it's important that Excel work harder to fix this issue given that a huge portion of its user base is stuck using it for a purpose it apparently can't support (metadata involving number strings like ISBNs and other long numeric identifiers) half my rage would dissipate if they'd just change the setting description to be accurate and stop claiming that it does something that it very clearly doesn't. For the record.
Scott McGee commented
The problem is that MS does not give the user control. MS knows better what's good for you!
Pretty much anytime the user isn't given a choice, it's gonna be a point of pain.
even if MS gives us the choice NOT to auto-switch to SN, a "standard number" won't likely be an option, at least not immediately.
this is due to the "15 digits of precision" they are so proud of, which is apparently embedded deeply in their code(?)
the best you could hope for is to set the digits as text whenever there were more than 15 of them.
text, for some reason, never gets altered...
While there are tons of issues with the "default to scientific notation" and other Microsoft autotyping, I think it's important to understand technically what's going on behind the scenes. Some people think Excel should just be magic and treat things as non-numbers, despite it being a piece of software for numeric data crunching.
This will give a good starter explanation as to why sometimes cells are Text and Sometimes numbers. Excel _sort of_ follows these concepts. https://support.office.com/en-us/article/Introduction-to-data-types-and-field-properties-30AD644F-946C-442E-8BD2-BE067361987C
That said, I STILL want Excel to give us the option to set the default assumption for long numbers to be a standard number rather than scientific notation.
If you need a quick way to fix a column from number to text, this VBA macro should help... I map it to "Control-t".
Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 2) _
Excel is good for a lot of things. The problem is, I don't use Excel by choice. I use it because the clients of my software use it.
For example, they might get a spreadsheet of device serial numbers (phones, tablets, computers) with associated IMEI numbers. When Excel converts the unique numeric serial number to "Scientific Notation", like a dummy, it breaks their data. When Excel converts that numeric IMEI number to "Scientific Notation", like a dummy, it breaks their data.
My clients don't call Microsoft. They call me. They think my software is jacked up and is converting their data to "Scientific Notation".
I can't convince millions of business users to use something else.
i'll be honest, i still use Excel for some things (since i already bought it), but some things i have to use Google Sheets for now, if i want them to work properly. but it's hard to recommend Excel anymore, when it doesn't "just work" under every circumstance...
I already left Excel due to this issue. It brakes so many things down stream.
Just stop using excel.
Microsoft are you hearing, this issue is HUGE and you are loosing clients due to this...
i agree wholeheartedly with Chris Hunt & Lee. it's becoming a hassle to use Excel.
if somebody types in 15+ very distinct numbers, odds are good they wanted them there. and if not, they could always MANUALLY change them to display as Scientific Notation.
i've said it before, but i HATE it when programs try to outsmart me. if you want to make it helpful, and you absolutely HAVE to do some smart BS in the background, why not PRESERVE my data by making it a text field instead? i was using Excel to organize text LONG before i learned to use it for numbers, and if it can't hold longer numbers, i'd be quite fine with making them text instead...
They said over a year ago this was going to be fixed but we've had a major product update without it. I wrestle with this terrible functionality every single day. The remarkable thing is NOBODY uses scientific notation! Why automatically convert data to a format that nobody wants?
Worse is that one you get it worked around, you change the column to text and re-paste all your data, you get the dreaded green corner and excel fighting you to remove leading zeros and once again converting to a format you don't want. You have to be careful not to cursor into those cells as excel will turn it into a number whether you like it or not. It's a form of business hazing.