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]
MS actually USE Scientific Notation.
that's why their numbers are always wrong, lol
is this done yet?
Does MS actually use their own products? This specific bug is absolutely enraging
You're forcing me to use Google Sheets instead which does not have this issue. I can't even pre-format the cells and then paste into them. At least give us the option to paste long numbers.
M. Chapra commented
I have to say, this 'feature' has actually caused me anxiety.
what i'm saying is, your workaround doesn't change the fact that the numbers saved will not be accurate. Excel, even though it SAYS it is in Number format, is still truncating data, just like it does in SN mode. any number higher than 15 chars is reduced to rubbish, and no longer accurate. the Scientific Notation thing is annoying enough, without Excel changing our data.
THAT is why he was saying to set it to Text instead of Number format. in Text format, your data is more safe...
@Roy, You're right on the mark... Double thumbs up to ya.
Ah, and to be explicit, it does the import just fine, leading 0's and whatever. Sadly, it then feeds that through its "every bit of entry work you ever do" engine to decide how to store each cell produced. So that is where the real problem is, that engine that goes to work when you press Enter or Tab or mouse away after every bit of typing, and before it displays pastings, and so on.
Not the import itself.
Yes, the issue is multi-faceted and a hassle and annoying.
Connor's idea and explanation are fine, and actually, most of us are forced to find the capability elsewhere, but it works fine.
WHEN Excel lets it go into action. Which is does not with CSV files, hence my earlier reply to Connor. You simply never get the chance.
If I type text into a cell it may widen to take it, but never, no not ever, for numbers. You get the annoying counter-explicit-format change to SN. Leading 0's are dropped. That's an ISSUE folks. Trailing 0's get dropped too. (If they didn't, one could reasonably easily apply one's own approach to the significant figures (religious war) needs, but you simply "can't even" (like the kid who could only speak the odd numbers) because information required for the issue is simply stripped away.
Yeah, there's lots of "you ought've..." kind of stuff, but you know, if I EXXPLICITLY formatted a cell Excel should absolutely never, never, ever ovverride that and nothing should EVER be stripped from my entry. What if lower case g's were deemed unneeded by Excel? (Just some April Fools joke, hopefully, gone the next day) And "golf" was automatically, it's just GONE, stripped down to "olf"? No recovery, no restoring it while chiding yourself to remember it was to happen and plan ahead next time. Just gone. Zero may be special, but it's largely the same thing. Period.
NEVER ever, in all the times I've seen this addressed here or anywhere else, have I ever seen a comment like "Um, I actually count on it to do that." Not freaking ever.
So we have to physically widen columns, then coax the display back to numerals, not SN, we have to live with the lost leading and trailing 0's, we have text entries like Corin mentions that can be nightmares (yes, you see them and make them right, but import 20,000 rows and one's buried in row 13,485, and it creates a number that is not 30 magnitudes out of whack, but just maybe three or four magnitudes and a value calculated from numbers in the column is sudden VERY wrong, but in a "the total was seven times too high, but I never noticed that other than to note things using THAT total seemed a mite high" way, not "it was four billion times too high so I noticed something was wrong" way. "Silent errors" if you like. Who amongst us has a suite of conditional formatting and special auditing sums applied to his data inputs... and the time to check them thoroughly each time data is brought in? Alright, plenty of us make some attempt, but very few are able to make comprehensive checks to the problem Corin notes can kill ya.
It's just all bad and we'd like it to not be. The Import Wizard had the wonderful feature of being able to tell Excel "nah, you just do it for me" by clicking "Finish" at any point. NOT available in pretty much any other "smart" features.
No one claims to benefit from any of it and to prefer it. Lots of us have been annoyed or screwed. So why not get to work and fix it all?
Oh, yeah, the real workarounds for the CSV problem pretty much involve the TXT extension replacing the CSV extension. That's a real good point by someone... could've sworn I saw a comment with that flash by here. Oh well.
One thing else about Excel's handling of all this is that your settings in the Import Wizard vis-a-vis the delimiters affect a copy from another program, a browser perhaps, and paste into Excel. That's why sometimes you copy and paste something and it goes all into one cell, multiple lined, or it goes into several cells, but all one column, and some other times it goes one word-equivalent or so into lots of cells and rows. So you get a payment card email and usually just copy and paste the email's data into an input area then read the output to perform the charge, but no, today is after yesterday's work which involved importing and today that paste overwrites 12 columns instead of being 23 rows in a single column. And the user calls you, badly describes things, and thinks your spreadsheet is garbage, then is hard to lead through solving the issue. Thank you Excel. They could separate functionalities there too while solving this and I would not complain!
One of the main problems with the importing CSV files is that Excel does not follow all the rules of a proper CSV file import. CSV's are supposed to always treat quoted text as text. But even if you have a number in quotes, Excel will still treat it as a number, not text as desired. So as @Corin Dennison said, "01200E05827005" would still get imported as a "Scientific Notation" number.
In addition, a Text formatted column should always export to a CSV with Quotes, but Excel doesn't unless there are special characters embedded that require quotes, like a space character.
Opening CSV files never open the Text Import wizard because the file extension implies that it's columnar data. .TXT files, however can open with the Import Wizard depending on how you open it.
Bottom line is that the only way to have a long number imported w/o potentially translating it to Sci. Notat. is to have the data in a TXT file and use the File Import wizard to convert the number column to text.
This is less than ideal for sure. An option to not convert to Sci Notat would certainly help especially if other data in the column is not in Sci. Notat.
Corin Dennison commented
Or Heaven forbid it was a string like 01200E05827005. Then Excel decides it is scientific notation despite the fact that most of the values above and below it are clearly not even close to resembling this, drops any extra 0s, makes it short enough, etc. And your data is irretrievable at that point. Text import is a solid, if hacky, workaround for this and Connor's explanation is easy to follow and correct. (although I rename the file to .txt instead of starting a new doc and going through the menu)
The real problem is not limited to importing text data. Lets say you're entering a 15 digit (all numeric) part numbers into a cell. Excel will automatically reformat you're part number into scientific notation. Why? It needs to stay as 15 digits. And sometimes, you might even want leading zeros retained.
Jane Doe commented
@once you go zak...once i re-format and save my workbook, it never reverts to scientific notation :)
Congrats on that personal problem of yours.
okay then Jane, and when you save it as number format, with numbers longer than 15 digits, close Excel and reopen the file. congratulations! your number has been truncated, thanks to Microsoft Excel...
Jane Doe commented
Connor - your work around makes zero sense.
Just open the workbook, highlight the columns/fields, and select Number format.
It changes any scientific notation back to its number format, and sets the format for selection.
Like who uses Text Import Wizard anymore? It's a legacy feature and most don't even have enabled.
Your pathway steps aren't even correct.
Yeah, see, the thing is... for a CSV file Excel 99.9999% of the time opens it directly without ever going to the Text Wizard.
So, you know, we CAN'T do those things.
Ball: still in Microsoft's court.
are you guys using the Text Import Wizard?
Open a new Excel file, then go to Open and find your .csv file (or other file type) containing the data you want to import. In the Open window, you will need to change the dropdown in the bottom right to All Files in order to see your .csv file. Click your file and hit Open.
you will then be brought to the Text Import Wizard. Go through the wizard...if you have a .csv then choose Delimited on the first page, Comma on the second page.
here's the key...on the last page (step 3 of 3) click on the column you are having trouble with in the Data Preview window at the bottom. then at the top, chose Text for the column data format. That column will now be formatted as Text instead of General. Then click finish.
Your data should then stop converting over to scientific. This is such a simple fix, but it took me forever to finally figure this out, so I thought I would share it. Let me know if it works for you.
@Anonymous, Let's see, what can we expect from a Monopoly.
Go to Jail
Go Directly to Jail
Do not pass Go
Do not collect $200
Sounds about right huh?
It's our own..... We shouldn't have abandoned lotus all those years ago.....
What sort od behaviour can you expect from a monopoly
You cannot really blame Microsoft. They just did what ALL drug pushers do. They gave the product away for free, and got people hooked on it. Then, they started charging for it, after people developed systems built around it, and integrated it into their lives.
And of course, to PROFIT off of it, they started CUTTING the Office programs with FILLER and FLUFF, like Scientific Notation, and Autoconversion garbage, so that they could justify a "new release" of "Office Whatever-Year-This-Happens-To-Be". And when that wasn't enough money for them, they pushed subscriptions, so that you can only get your fix if you pay a monthly (read: "annual") fee.
And now we're so damaged by the filler we can't even function properly. PLEASE stop the auto-SN madness...
This is ^&^* ridiculous. Just give us a setting 'never ever ever never ever never ever use scientific notation". Why is this so hard? I am going crazy with Amazon downloaded into being imported into Excel and Excel *insisting* on changing to scientific notation. Get it done already!