Technology

How about just not auto-convert everything and keep the integrity of the data unless specifically asked to? Is that so hard?

Me before reading the article: It's got to be dates. Excel thinks everything is a date.

Me after reading the article: Even the workaround is halfhearted. Jeebus.

The idea that any scientist is doing data analysis in Excel is honestly terrifying on every level.

Thank god! You have no idea how awful this is for scientists. Need to paste some gene names down? Better hope it’s not MARCHF8 or in the Septin gene family, otherwise you have to convert columns to text then import the data. Seems like a simple fix, but many wet lab biologists are technologically challenged.

"Microsoft’s blog adds caveats, such as that Excel avoids the conversion by saving the data as text, which means the data may not work for calculations later. There’s also a known issue where you can’t disable the conversions when running macros. "

This sounds very half assed...

It's no good having this as part of the user options. It should be a sheet characteristic and the default should be "keep cells exactly as entered regardless of data type".

Now if only it would stop dropping leading zeros unless you ask it, and we got rid of the MM/DD/yyyy date format entirely.

This is the best summary I could come up with:

In 2020, scientists decided just to rework the alphanumeric symbols they used to represent genes rather than try to deal with an Excel feature that was interpreting their names as dates and (un)helpfully reformatting them automatically.

Yesterday, a member of the Excel team posted that the company is rolling out an update on Windows and macOS to fix that.

Excel’s automatic conversions are intended to make it easier and faster to input certain types of commonly entered data — numbers and dates, for instance.

But for scientists using quick shorthand to make things legible, it could ruin published, peer-reviewed data, as a 2016 study found.

Microsoft detailed the update in a blog post this week, adding a checkbox labeled “Convert continuous letters and numbers to a date.” You can probably guess what that toggles.

The update builds on the Automatic Data Conversions settings the company added last year, which included the option for Excel to warn you when it’s about to get extra helpful and let you load your file without automatic conversion so you can ensure nothing will be screwed up by it.

The original article contains 225 words, the summary contains 184 words. Saved 18%. I'm a bot and I'm open source!

20 years after the problem was first reported.

Meaning there's still hope for XDG support in Firefox?

Microsoft fixes one of the Excel features that wreck scientific data.

This isn't a fix. Excel wasn't meant for this. While I do understand it's convenient as a database, unless you're doing something unimportant and small you just really should use something proper. And even now that this "problem" is gone, I am certain there are still more things that cause trouble. You can not satisfy everyone and Excel was just... not made for gene info storage.

Even if you don't want to use stuff that isn't Microsoft Office, that comes with Microsoft Access, which is a proper database management system. It's literally in the same software package, so why do people refuse to use it?

It's too late though, scientists already had to rename the genes. Although of course there are other things that can trigger it, not just in science.

From the article:

The problem of Excel software (Microsoft Corp., Redmond, WA, USA) inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004 [1]. For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively. Furthermore, RIKEN identifiers were described to be automatically converted to floating point numbers (i.e. from accession ‘2310009E13’ to ‘2.31E+13’). Since that report, we have uncovered further instances where gene symbols were converted to dates in supplementary data of recently published papers (e.g. ‘SEPT2’ converted to ‘2006/09/02’). This suggests that gene name errors continue to be a problem in supplementary files accompanying articles.

What about text selection knowing better what I want to select?