> Convert a continuous string of letters and numbers to a date.<p>This has been a massive bugbear of mine. Particularly when it inexplicably chooses USA date formats even when faced with a column containing values like 15-07-75. It would frequently convert half the values into US date format where possible and leave others like above unconverted.
Damn near 50 years late. Did the memo that literally everyone hates this feature, and it’s spoiled many scientific studies and brought down multiple businesses just get delivered to their developers?
It blows my mind that the behavior in spreadsheet software is <i>not</i> what I would expect:<p>* NEVER modify the text I type into a cell.<p>* Parse, interpret, format the cell according to the "data type" that's detected or chosen by the user<p>* In the grid, display the data according to the interpretation above<p>* In the formula textbox, show me exactly what I typed in, unmodified.
At last.<p>I hope anybody who is thinking of implementing a 'computer knows best' feature with ML reflects on how annoying this feature has been over the years.
I have worked several times on serveral projects with excel files containing barcode number like 0754...<p>And someone in the process open the file, and hop excel remove all the zero at he beginning fo the number<p>I've lost a lot of time from this...
I guess the gene name changes will not be reverted? <a href="https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates" rel="nofollow noreferrer">https://www.theverge.com/2020/8/6/21355674/human-genes-renam...</a>
With CSV files you get much better experience on Windows when you use "Data -> From Text/SV" instead of just opening the CSV file. You get a proper preview, can tweak the field types, set decimal separators etc.<p>This also creates a connection to the CSV file and you can also easily refresh data. With pivot tables this makes it possible to build simple reporting solutions. Just place updated csv files to known location, refresh data and tables.
I've said for years that whenever Microsoft guesses what I want, it's wrong. My context is as a software developer, so I get that I'm not their target demographic with an office suite, but I stand by my point.
By the Nine Divines! I'm still just a spreadsheet initiate, but that auto formatting was steadily becoming my nemesis. I can hardly conceive of the headaches this has caused in people who make serious use of them. It's hard to believe it took this long to implement a feature that seems like it should have been there from the start.
Great! I am saying in the last 10 years: all I need is a button in Excel called "I am a power user, don't do anything unless told"
We are getting there :)
Which version of Excel has this?<p>This confused me: (and is 2309 = 23H2?) (is Win10 supported? Why does it require latest Win 11?)<p><pre><code> ~~~~ excerpt ~~~~
</code></pre>
This feature is available to all users running:<p>Windows: Version 2309 (Build 16808.10000) or later<p>Mac: Version 16.77 (Build 23091003) or later
Still remember that dna data was saved and edited inside of excel and somehow those huge strings of data were randomly false in part, because of automatic data conversion to arbitrary data formats.
Inconveniently, they are only doing this now, after the last non-subscription version of Office.<p>And conversely people will then more frequently wonder why their supposed numbers and dates cause errors in formulas.
So not that libreoffice calc is as feature-ful as excel but AFAIK it gives you options on how to autoconvert data every time you import or copy and paste table like data into it including not converting.
This will help us, not because we worked with genetics, we have far more mundane requirements: occasionally inspect csv import files, that contain externally relevant ids, of the format "00012345678912345". Turning those into "1.2345E13" is no help at all.<p>Yes, VSCode with an appropriate plugin is IMHO better than excel at this, but some people (e.g. business analysts) will automatically reach for excel and have to be walked through setting up VSCode and the plugin.<p>Ids are not really numbers, even if they look like them.
Excel is wildly popular. Good chance it's the dominant programming tool by user count.<p>Excel is broken, terrible crap.<p>Both of those are true at the same time. There is surely a market opportunity there.
It would be nice if Excel had a builtin "double bookkeeping" feature: given a formula, ensure results are also calculated or accounted for in another place.<p>It would be cumbersome to always have it enabled, so it could certainly be disabled by default. But Microsoft could market the feature with suggestive tooltips.<p>One example is if I have the fornula `=A1+B1` in cell C1, I can go to a separate worksheet and generate a constraint like `=MUST(W1!C:C<1337)`; then Excel would flag any rows where the calculation is false (≥1,337).<p>Of course, this kind of goes into treating derived cells as constrained types, but it seems sanity is achieved with the easier checks.<p>Constraints or properties are nice in that they are not unit tests; they could be added at the "moment of instantiation" like an object constructor--but in this case, the violation occurs as a post hoc check. It has to happen first.<p>You might say, "I always triple-check my models and ensure worksheets are equal in multiple ways." Maybe it's possible to do it already. Sometimes, quality is about introducing <i>frictive utility</i> with minimal overhead.<p>The problems solved are usually not handled with only with an integer primitive, but hand in hand with a domain component that makes us pause and go, "Okay, I guess a person's age won't be MAX_VALUE or negative."
I work technical support at a startup where we have a self-service Uploader for some data imports. Can't count how many hours and emails I spent going back and forth to tell people how to turn auto conversion to scientific notation off. Long overdue feature/setting for them to release.
I hate when excel converts UPCs to decimal and no amount of format will fix it other than to insert a colin before the UPC which is apparently the official answer on what to do
I think the "oh, finally" here is somewhat overblown. It would be much more annoying to lots of people if Excel did not recognize a number as a number, or a date as a date. This convenient conversion feature could always be overridden, if I'm not mistaken, by either pre-formatting cells as text (when you're entering manually), or designating a column as text (when importing, and converting from, a CSV file).<p>One could criticise that instead of 1. trying to determine the type of a column in CVS, then 2. treating all values of the column as instances of that type, Excel would go through row by row and decide ad-hoc which values to auto-convert. That might have been done because people don't just store relations in CSV, but use it as a lingua franca format to move things between applications.<p>The designers of Excel were not idiots, and tried to build a tool usable by the average user.
Great! Now solve production data being destructively edited by management opening random files in Excel.<p>Mine was one of the early COVID test results lost when someone ran medical data through Excel. As expected, the account numbers didn't survive.
And when will they finally allow to change formula language to be independent of display language?<p>In German locales the parameter separator is ; rather than , which makes copying code from others a nightmare.