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]
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?
@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.
@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.
indeed, Todd. and we assume that our data will not be corrupted, because, other than being changed to SN, there is no indicator when opening a CSV that the data will be altered.
there is no popup that states "WARNING: your data will be truncated after 15 characters, and converted to a non-text format, just for being so long."
i mean, can Excel TRULY say that it supports CSV files if it doesn't adhere to the standards they use?
what other program would you EXPECT your data to be altered without warning???
For clarity, here is the problematic business use case:
User has an external system which generates a data file in CSV format and which contains numerical ID data elements which are several characters long.
The user needs to view the data and possibly make adjustments to some data elements.
The user wants to use Excel for this activity.
The user assumes that the data integrity will be retained in its original form upon opening the file using Excel.
The user assumes that only the manual edits made by the user will impact the data set.
The user assumes that savings the file in its naitive CSV format will retain all of the original data values as per their state before opening in Excel, except for any manual edits made by the user.
The saved CSV file has the same data structure as the original.
The saved data file can be imported into another external system without fear of data integrity conflicts.
This is what the users are often doing. With the scientific notation handling currently in excel the integrity of the date within a CVS file is unintetionally changed by the act of simply viewing the file in Excel and saving it in CSV format. When the data elements are significantly long strings of numbers, the scientific notation destroys the string and rounds it up to a aggregated numerical equivalent which corrupts the intent of the data element's "ID" value.
On the contrary, I click a CSV file and hope it does NOT read my mind. The real problem is that it tries and fails. The ask here is for it not to try, or at the very least to have a setting to tell it not to try.
The fact that I never knew about Power Query until coming here, or that it was never even mentioned in an entire (admittedly useless and annoyingly required) college course on the Office suite means the learning curve is not nil. Just knowing it exists takes some digging apparently. I get that it seems obvious to a power user, but one of the hardest things about being good at something or smart in general is realizing what other people know or find intuitive to figure out. There is a reason UX is an entire well-paid dicipline
yes, but it should be COMPLETELY unnecessary, if Excel would just see that it is a CSV, and not treat it the way it does every other Excel file...
Power Query isn't a third party addon. And for importing text files, the learning curve is almost nil. It is far easier to use than the depreciated Text Import wizard, and far more accurate than just clicking on a CSV file and hoping it reads your mind.
i agree with Corin. one of the nice things about Excel has always been that it can be as simple or complex as you need it. i was using it for plaintext spreadsheets LONG before i learnt the more advanced features, and many people who use it do not know computer technology well, and would be tripped up by having to use some 3rd party addon that isn't on all versions of Excel, just to do something that it seems obvious should be simple to do.
leave the complicated addons to the more advanced users who are familiar with them, and just program Excel to not alter data after it is entered or opened...
I imagine you are correct in that most of the people in here are not having the same issue as me, but the title technically describes my issue accurately.
I have since changed jobs and no longer use excel for this sort of thing but it strikes me as strange that you need to learn Power Query to make this happen. What if you don't want control so much as a bunch of text values in cells. This makes the learning curve of using Excel for really basic things way steeper, not to mention it is a bit of a gotcha.
@corin - what you refer to is not the same thing. A part number with an "E" in it is not the same issue, but you are correct, Excel will try to convert to a number thinking it is scientific notation, unless you use Power Query (which you should) where you can tell it to leave that field as text. No one should be importing CSV files via plain old Excel if they want any level of control. Import via Power Query and get 100% control over every field.
If you type in 123456789 into a narrow cell, it will change it to 1.2E+08 visually, but if you change the cell format to comma, currency, or whatever, it has retained 123456789 as the full precision.
"The issue only becomes an actual data problem when there are more than 15 digits."
This is not true. If you have a csv file with GUIDs that sometimes contain the letter E, you can encounter data corruption even if the numbers are smaller than 15 digits, as it can remove 0s from various spots in the GUID (at a minimum) when it assumes the presence of the letter E indicates scientific notation. In my particular issue, csv files were being exported from the database client and opened in Excel, leading to data corruption. This corruption was not even immediately obvious (since it only applied to some rows) making the problem even worse.
MSFT don't listen commented
Except it's not about 15 digit precision.
If I type a 9 digit number into a standard 8.43 width cell, I get the scientific format.
I'd much rather get the ###s that occur in other circumstances
Ok. that is massive. M.******I.V.E. They could be working on that, but that would literally involve changing every single function that deals numbers (so all but the text functions), tables, pivot tables, Power Query, Power Pivot, Power Map, the Power BI enterprise app, Data Gateway, which all rely on the same 15 digit precision.
that's what i'm saying. just change how many digits it stores, and then display however you want...