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]
Martin Douglas commented
Simply set the cell format to Custom. Then enter just a question mark.
I don't know how this is classed as a feature request; it a bug fix request
Pat Babcock commented
Well, after over 4 years, it's clear that they aren't going to change this behavior. As entertaining as I find the constant stream of comments and supplications, it's time to unsubscribe from this useless list...
i understand that. but the problem is not just an exponent. it is the whole "Scientific Notation" thing in the first place.
unless that was just a joke that i didn't get. i am pretty dense, y'know ;p
Marshall Ringler commented
@zak: That is exactly what this is all about. Scientific notation is a number "times ten to the power of X". The "power of x" part of that is an exponent.
@Sharon: who said anything about exponents? are you sure you posted on the right page...
Sharon Tirrell commented
Hi Not the perfect solution but the fastest solution I have found. Add a column in your data and use the following formula.
In this case column A would contain the field that has the exponent issue.
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...