Be able to turn off auto-conversion of fractions and words into dates
When I import data in the format of a fraction #/#, excel automatically converts this into a date. This is unnecessary and should not be a default operation. At least, there should be a simple way to undo this.
I work with biological data, and there are gene names like "Oct1", and these get converted into dates, causing errors in data analysis.
Anonymous
shared this idea
Hi,
You can do this already in Excel today – there are two ways:
1. You can type an apostrophe before the data you enter → ’Oct1, and Excel will then not interpret that as a date (it will interpret it as Text).
2. If you format the cell as Text before typing the value in the cell, Excel will not interpret it as a date.
Hope that helps,
Dan [MS]
4 comments
Comments are closed-
Anonymous
commented
Remember that if you do not use an apostrophe, if you later change the formatting by accident, it will mess up the date.
-
Anonymous
commented
Hi Dan,
no you can't do that if you try to paste data into Excel. It will change the cell format right back.
-
Diana
commented
This is similar to https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually?tracking_code=ad3cee9086349d4e8c7864f31c9419e5. As described numerous times, manual solutions are insufficient.
-
Anonymous
commented
That is beside the point. I did not say that it is not possible to do.
This is a very widespread problem in the scientific community. If you need proof, here is a link to a recent paper: that has been viewed over 56000 times.
https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7
"Gene name errors are widespread in the scientific literature"
"...The spreadsheet software Microsoft Excel, when used with default settings, is known to convert gene names to dates and floating-point numbers. A programmatic scan of leading genomics journals reveals that approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions."If this many scientists have problems with Excel, this is not a trivial problem.
The paper also points out that Google Sheets does not suffer from this problem.Mengshu