3 ms·
> Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked f
by dragonwriter 3y ago
> Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix.
I don’t believe you’ve been doing it that long and haven't found the fix.
Apply appropriate formatting if you have long strings of numeric digits [as long as they can’t have leading zeroes] that you want stored as numbers but displayed without scientific notation. (Not the best choice for what is semantically a date, in your case, but...that’s a whole bigger issue and, well, babysteps.) You don't have to look at anything, just do it for the ranges where that is the kind of data:
https://help.godatafeed.com/hc/en-us/articles/360049916591-How-to-prevent-Excel-from-converting-your-UPCs-or-GTINs-into-scientific-notation https://help.godatafeed.com/hc/en-us/articles/360049916591-H...
- deleted 3y ago[deleted]
- mulmen 3y agoThe text import wizard does work. The problem with it is that everyone has to use it and if anyone doesn’t then your data is silently mangled. In the past I have added an apostrophe to the front of any integer that should be displayed as a string. Excel will not display that leading single apostrophe. It of course taints that data forever but I consider “Exported to Excel” a terminal state anyway.
- Alupis 3y agoI cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"... It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes... it'll wreck every single one every single time, unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc. Try working with a spreadsheet full of database id's... same thing. It's asinine. By the very nature of Excel, large numbers belong as-is... ie. you're studying something and need the values. Approximations (what scientific notation gives you in the best case) are an exception to what people need nearly every time.
- dragonwriter 3y ago> I cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"... Okay, so set formats appropriate to what is useful for your data. Defaults don't cover all cases, otherwise there would be no non-default options. > It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes... Codes aren’t numbers they are strings, even if the code is a sequence of digits. They should be formatted as text. For actual numbers that shouldn't be displayed in scientific missions, the appropriate numeric format does that. > it'll wreck every single one every single time If its any code that can be safely stored as a number, it won't wreck it. It’ll just look bad until its fixed. If it, say, has significant leading zerores, yes, it’ll wreck it, because excel defaults to dealing with numbers, not text, and there is a difference between text made up of digits and actual numbers. > unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc Knowing the shape of data you are entering and selecting a column and applying an appropriate format (including “text” as an option) before you enter any data (usually, to a whole column) isn’t that arduous.
- Alupis 3y agoThe defaults should be sane - that's the point. What percentage of Excel users actually want data automatically converted into Scientific Notation? Close to zero I'd wager. Your offered work-arounds only apply in a very narrow workflow. I work with Excel nearly every day, all kinds of files (CSV, TXT, XLS/X, etc). There's so many ways this issue can bite you unexpectedly - causing you to save and permanently destroy data. If I type/paste/import/download/whatever `01234567890987654321` into a cell, 100% of the time I do not want `1.23+E18`. I don't want to even see that, I want to see the original value I entered. It's asinine. Excel may be the most used Office Suite app, but it's by far the most painful to use. The Excel Team has a long history of justifying downright perplexing behavior. Remember when Excel was the only Office app that didn't support Snap-To back in the Win Vista/7 days? Joel had a long-winded explanation that boiled down to "just cause"... it was perplexing even at the time. It really makes one wonder how bad the rat nest of a codebase Excel is. "There's ways around our astonishing default behavior so just deal with it". That's not a great answer.