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]
@Scott McGee: Yeah, if it were worded differently. But it was not so he meant the tobacco kind.
Also, PQ has its own issues. I like it for handling things that I used to have to do piecewise, and/or repeatedly. And toher things. It's just great, in general.
But... it still strips the leading zeros from common CSV files BEFORE you can ever interact with the data (and protect it!). It still imports long numeral strings formatted as General even though you put in a step to "Change Type" to Text. Small, but real aggravations, which eahc need their own attention and workarounds: at least it DOES preserve the data for me to then work on dressing up all such things, which, as I said, is soooo much better than data loss before I even get to touch it, but... it's not truly ideal. The simple Import Wizard did apply the Text format to its output so no followup was needed.
The biggest thing is so many users just really have the ability to just open the CSV file, at least vs. interact with PQ. So while it's wonderful to have so many things now available in an import, I still want to have the basic function so many users take for granted and want to use... I just want it to work right, not work crippled from the get-go.
It's like I said for many years, when there was a point, computers have to become like toasters for users of them to become ubiquitous. The average user never wanted to HAVE to control and decide on so many things with Windows computers (hardware, for example), they just wanted them to work out of the box, as they expected. And... not have to pay $5,000 extra to Apple for the privilege. Now you buy a phone or tablet and that actually, mostly, happens, and ubiquity is no longer a discussion topic. Same here. They don't want to "do this and that stuff, then it will work like you think, except of course, for the permanently lost data...": They just want to click and it works.
Which, in this case, is just so insanely not unreasonable and is actually more or less touted as the reason behind what MS does here. So not unreasonable that it seems like it really ought to be.
Of course, the flip side is job security...
yeah, i was actually referencing a George Carlin bit.
somebody was freaking out and he said those words.
i didn't expect anyone to get it, but i didn't even know dip had another meaning!
Scott McGee commented
@Roy - You use chips to get the dip.
Scott McGee commented
My pain has diminished significantly with the maturation of Power Query, the application stupidly re-branded at "Get & Transform" in demonstration of the utter incompetence of some at MS. With Power Query, I can import data cleanly, with the native datatypes intact, and I can fix just about any problem with the content within the fields (so far, anyway).
The big, big, 10,000 foot view is that you can never, NEVER, ***NEVER*** - never, never, never, never, NEVER - NEVER - never, never, never, never, never, never, never, never, ***NEVER*** assume you know what the user wants to happen on a data import better than the user knows. Never. You must, must, must, must give the user CONTROL and not assume - tho allowing the user to say "yeah, go ahead and assume as a default" and then have the OPTION of clicking that assumption off, cleanly, easily, simply, clearly - THAT would work.
There should be some "mantras" or sayings that are embedded deeply and permanently into the cellular structure of each member of the Excel development team, and one of them should be to always give the user control. Another is to recognize with crystal clarity that a fundamental design flaw today can potentially cause immeasurable user pain for years and years and years. Don't be that guy!
Karen Grube commented
I am often given Excel files to import into SQL Server where the numbers appear as Scientific Notation. These are columns like a Part Number or an Invoice Number that are absolutely NOT meant to be treated as numbers. Before I can import this data, I have to REFORMAT the column as Text and then go in, filter on the "+" sign in those columns, and see what's really there. Often, what I see in the column is different from what I see in the text in the input prompt, which is often the real underlying number. That is so awkward!!! Please just quit turning ANY column that is not set as scientific notation format into scientific notation.
@Gary: Yep, there is always that flip side. But at least it is non-destructive: you can DO something about it afterward on a full, complete data set, not a destructively altered one that simply cannot be reliably restored to its orginial state.
But absolutely, there is that flip side. Another flip side: there are tons of people who just don't care, or actively like it. Fewer tons than don't like it, I do believe, but tons and tons nonetheless.
For me, above all the other aggravations about it and all the utter stupidity of it, is the so simple fact that if Excel simply engaged the longtime, basic, no frills Import Wizard, almost all the aggravation would be gone because I WOULD (and have done for decades with other file types) simply tell it to import those columns I know ahead of time to be impacted as Text. And like for years with other file types, the absolute first thing I check is for that issue, and related ones like changes from numerals to dates with the concomittant change from original numerals to the date integers. So easy a thing and it would solve so many people's problems, taking this problem down at least a magnitude.
Just engage that simple, straightforward Import Wizard. Just that.
And for those who don't care, they have a simple click of cancel to overcome the change and get a mindless dump and change. A hugely slight extra bit for them solves nightmares for so many others.
But... they won't even stir themselves to do that little thing.
And Zak, enjoy the dip. Like ex-running back Walt Garrison used to say as he encouraged millions to court a series of "you-don't-have-a-face-and/or-throat-anymore" cancers with "Just a pinch between your cheek and gum..." (you know, so long as you still have the cheek and/or the gums)... No, forget that. Toss the dip in the trash and skip pushing it on me.
One eye-opening day in my past, not formative, just eye-opening, was my parents moving to NC, outside Asheville, and one visit sitting in the little valley's bottomland matriarch Ma Huggins' living room gradually meeting much of her extended family and having a 6yo girl and a 7yo boy both, during my time on the same couch, pull out well-worn "tobaccy" pouches (you know, the old zip up kind bigger than a woman's clutch style purse, and pull out wads of new tobaccy to replace the... used... wads they'd just cleared out (cleared, not cleaned, I'm still sure). 1st and 2nd grade and they had their own well-used tobaccy pouches, filled with fresh tobaccy from their drying sheds... That needs to die out, not go wild, so keep the dip idea to yourself or your speeches in the park while families are just trying to have a pleasant picnic in the sun, thank you.
I would agree with @Gary. I think there are really two paths MS needs to follow here.
First path is just a generic setting for Excel, deep in their autoformatting and autocolumntyping that allows a user to say "Leave long/double values as found, do not use Scientific Notation". And possibly a sub click that says "Treat values over x characters as text".
For a certain class of user, they will never want Scientific Notation as a default. Even if they want to treat these things as numbers.
Second though, all of the MS Office products have a super shoddy automated data import system. Which, for a data guy like me, works about 80% of the time. Which means I can't use it. So, one of the things I have to do is import everything via .csv everywhere, to prevent the automated things it assumes for Excel files in apps like Access.
And then, as another recent user mentioned, you can't really use .csv as a file to edit when you have data which is numeric appearing that autoconverts to Scientific Notation, because .csv's save what is displayed. And not the underlying data.
So, my system, Microsoft, is to maintain everything in Excel files that need to be Excel like, use the script I wrote for myself to quickly "convert to text" all of the columns affected by Scientific Notation, then save as Excel for editing, then save as .csv for my datasource for things like Access. (Because, I also have data types which are text, but are -mostly_ all numeric. And Access won't let me set the datatype on Excel linked tables! And it assumes they are numeric based on the first x rows (an issue for the last 20 years or so), and so I _have_ to use .csv or some other output to use the Excel data in Access.) *argh*
Totally with you all the way but looking forward to the idea of 'Blue Helicopter' formatting!
If you define something as text it should darn well stay as text and on the flip side I get data sets which are numbers that get defined as text - they don't get autocorrected.
Google sheets might be a viable option for single standalone workbooks but not as an interconnected option crossing databases, COM data add-ins. webExtracts, multiple single but connected workbooks and so on.
guys, Microsoft DGAF. If they wanted to fix this problem they would have. it can't be as difficult to make this change as updating MS Teams video chat to show multiple videos, and they were able to do that in 3 months.
We are all just screaming into the void. It does feel kind of good though. AAAAAAAAAAAAAAAAHHHHHH!!!!
have some dip.
Please, I am going to go crazy if this doesn't change, literally.
Geoff, honestly at this stage google sheets ios far superior and easier to use than Microsoft Excel. It keeps the numbers exactly as you want them to be and will even import excel documents very easily and all for free!
As for "WHY" a person needs it, it JUST DOESN'T MATTER. It is needed and that's the ever-loving end of it. Anyone at MS who thinks otherwise needs a new head.
In general, I'll point out that I am SICK TO DEATH of one loser after another in the computer world (including a persistent one on this site) telling me "NO MORON, you shouldn't ever do it that way and we won't let you!"
The worst of the awful situation? When you format it as text, at that moment it becomes literal text in that it is saved precisely as the word "horse" is saved. Because it CAN'Tbe saved the way Excel saves numbers. So there it is, all those lovely bytes, doing what's needed, and Excel actually goes to the work to save it as a number, chooses to work from left to right rather than right to left, and then pads it with zeros. And THEN it saves it into the CSV where each digit is AGAIN bytes like an "h" is bytes, not like a numeral is. Back and forth... apparently when THEY wish to do something with it, it is all just fine and dandy, not "you shouldn't ever" kind of stuff.
If it is formatted as text, currency, or as a blue helicopter, that's how it should be output into a CSV (or any other format) file. And as to import, Excel should do whatever it must to present exactly what was presented for import after it is imported. If there was a 40 digit number imported and that cannot be handled as a number, then it shouldn't be: it should be handled as text. NOT as "Oh heck, this looks number-y so it's now a number! Hey, Presto!"
SICK TO DEATH at being told "P*ss off a**hole."
Please continue to work on this! But don't forget large numbers not surviving saving to CSV while you do.
I have an 18 digit number in a txt document. I import as comma delimited, forcing text format for the number's column. It displays all 18 digits. Good. I save as .csv and reopen. The text formatting is gone and the number is now in scientific notation with a loss of precision (the last three numbers are 000. BAD!
I start over. I enter the formula ="="""&B2&"""", which results in =”345234523454353456” being displayed.
Upon creating the formula the data is presented as =”345234523454353456” literally.
After saving as csv and reopening, it loses one level of translation and is presented as 345234523454353456 without the = and “”.
After closing and reopening again it loses that last level and reverts to scientific notation.
Solution: If you can present a currency format of $123.00 and still manipulate it as a number, you can convert ALL numbers, including over 15 digits as their text equivalent by default, right justified, and still manipulate them behind the scenes as numbers.
This business of losing precision when displaying large numbers and saving to .csv has to stop.
Real world reason: Sometimes real estate parcel numbers are 18 digits long. We need to actually use all 18 digits, ya? Converting them to scientific notation, and/or saving them to .csv to upload to a crm or mailing company, only to have the last three digits converted to 000 is problematic.
How is Apple Numbers working out for you?
"WE STARTED WORKING ON IT"
This is why Mac is better. Microsoft just doesn't give a ****.
Yeah, there's NEVER a time when I want anything converted to scientific notation. For real (actually rounded to the displayed value), or for display.
But yeah, the CSV importing is really annoying. Here's a thought I can't make work if regarding MS as a sensible, clever, set of folks: if a CSV field starts with a "0", then it was considered important to save it with a leading "0" and therefore cleverness suggests it ought to be preserved in the import. And yet, it isn't. It is hard to see that as anything but stupid.
A series of digits with a decimal separator in them? I'd forgive someone thinking without asking that they are meant to be considered a number. A series of digits with appropriate separators for the installed version's national date or time system separators? OK, hard to argue with thinking that you want a date or time format. But it goes too widely for cleverness there.
And you know? I'd really rather it not do any of that but rather simply import everything as text exactly as it appears in the "it was important to output it like this so it's important to import it like this" CSV file. Or as an absolute minimum, say something like "The following fields seem to be non-textual information. Please follow the opened import wizard if you wish them to be imported as particular non-textual information, or close the wizard for them to be imported as textual information." You know, and actually do what it offers to do.
Data|From text/CSV IS actually useful and useable, but I shouldn't have to do so to overcome Excel's predelictions.
By the way, Excel only uses the first 8 records to decide how to import, unless you go to the trouble (and like in the play, that's Trouble with a capital T) to change it. That's why you get formats like minutes:seconds for a time column (or simply 2-3-4 digits with a ":" between them), not hours:minutes:seconds for some imports and similarly other situations. You CAN set it to look at a million if you lke, but not only does that slow things down (or used to, computers are much faster nowadays) because it looks for EVERY field, but it's Trouble with a capital T just to set. I'd have to hunt it down on the internet again to do it.
In the meantime though, anyone suggesting we use Data|From text/CSV is right about it working well. Just don't need to hear the smugness of their religious feelings for it most times they say it. It's like listening to a vegan. (Though they're not right, either factually, or "in the head," while these guys are, factually anyway.)
But MS gotsta fix it man! It shouldn't happen to need a workaround, shouldn't happen at all.
Patrick B. commented
It's a problem both when pasting and when importing CSV. It's an inexplicable show-stopper which prevents me from using Excel at all.
Ken Beard commented
I have now spent more time in this thread than it would take a MS developer to fix this.
if you look back to the first post, it refers to opening CSV files, and having the data changed, which in turn forces us to use the Import Wizard instead of just opening the file like any other supposedly supported file in Excel.