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]
more like Return of the Jedi, lol
Ed Hayes commented
'not the zak you're looking for'
Are you asking for the return of Clippy?
i personally don't like it when Excel AUTOMATICALLY does anything. i would prefer that, if the situation arose for such an option, it would pop up and ASK me what i would like to do.
doing things that may not be helpful is...not helpful.
especially for those of us who actually KNOW what they are doing, and what they WANT to do.
Haigha and Hatta commented
The anonymous comment where we can set the number of digits for a number to be converted to scientific notation would be best, and should be set as default. This is very essential when exporting files from web/desktop applications generating spreadsheets/Excel. Also, the permanent rounding up of numbers replacing other digits to 0 can really affect this kind of scenarios negatively. Maybe we can configure that as well.
Ben Clothier commented
Dear Microsoft, please fix this issue post haste so I can stop getting notifications from everyone and their dogs about how to work around this and actually subscribe to more useful feedback. Thank you.
@Anonymous2: Google Docs saves files as xlsx (Excel) files as an option.
and nobody here saw anything ;)
@Anonymous1: if you prepend numbers with an apostrophe ('), it will treat it as text, and not show the apostrophe in Excel.
of course, this is a massive pain in the neck to deal with if you must export the data again...
I am going to stop using excel and break my companies security policy just because of this. It's so frustratingly annoying that I literally do not care if I get fired because of this. I have to copy paste a LOT of numbers (most of which are actually text values) to excel and having 4 extra steps just to get the correct value is absolutely ********. It's also commonly droping off preceeding 0's, so then I go spend more wasted time to rationalize those.
I seriously doubt Microsoft will change this. This has been an issue that I have had since I started using Excel 20 years ago. There is no good way of converting the Excel mess up so it will match up with data outside of Excel, unless you do the editing outside of Excel.
Aside from Excel converting to scientific notation it will also remove leading zeros, which will make things not match up when doing database joins on data sets that use a certain number of characters for a record ID.
Nonetheless, every few years I search for the answer to change, but it never does.
At the very least, I wish Excel would treat them as strings when you wrap the data in double quotes.
handy trick, but it doesn't change the fact that Excel alters CSVs when you open them.
and to reiterate what has already been said, NO program should alter data without consulting the user.
i am very sick of software that tries to outsmart me. especially when i already know what i'm doing (in most cases at least). if they want to change things, they should at the very least consult me FIRST, or at best, give me an option in preferences to decide how i want it to treat such data.
I found out that if you use the concatenate function around the cell value, it prevents excel from doing the automatic conversion
This has caused major issues for me too! so annoying
This causes problems when loading data from CSVs into our CRM. Any editing on the CSV in excel at all (which is necessary) mangles the phone numbers. I really hope this is fixed soon.
This is nonsense. I can't stop it from either changing my long numbers to scientific notation or truncating and adding zeros, no matter how I format the cells. This is why I hate MS products. Ugggggh
@Anonymous: 5 months actually, but this thread was started over 3 years ago...
at the very least, Excel should be ASKING when you open a non-Excel file (like CSV) to see how users want to open it (e.g., use Scientific Notation or mark all as text).
i can't imagine a world where changing user data without asking was acceptable.
on a side note, you know, it would really make Excel 2019 seem like something worth upgrading to if you fixed this for it, since it has no major changes from 2016...
when is this going to be done MS? its been 6 month already and 0 updates...
Ed Hansberry commented
Barcodes should be treated as text as they can have leading 0s that any number format will remove. use Power Query to force text on the CSV import. Works every time.
I get alot of Spreadsheets with barcodes as CSV and have to keep converting barcodes back to numbers as barcodes UPS/EAN 12+ digits.
Insetad of turning off Scientific why not provide option to define what length number should be converted to scientific.
Default at 12 as now to keep current users happy.
Could be inserted into Excel->Options->Formulas->Calculation Options (Excel 2016).
Simple option box 12 to 100 digits should cover everyone.