6 ms·

This reminds me of this one from a couple of years ago: https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://w

This reminds me of this one from a couple of years ago: https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam... “Scientists rename human genes to stop Microsoft Excel from misreading them as dates” > …over the past year or so, some 27 human genes have been renamed, all because Microsoft Excel kept misreading their symbols as dates. > The problem isn’t as unexpected as it first sounds. Excel is a behemoth in the spreadsheet world and is regularly used by scientists to track their work and even conduct clinical trials. But its default settings were designed with more mundane applications in mind, so when a user inputs a gene’s alphanumeric symbol into a spreadsheet, like MARCH1 — short for “Membrane Associated Ring-CH-Type Finger 1” — Excel converts that into a date: 1-Mar. Original paper leading to the changes: https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...

ineedasername 4y ago
And I still can’t believe that after multiple decades it removes the leading “0” for things like zip codes. It does this even though the default data type for cells is “general” and not explicitly numeric. To my thinking that should cause excel to defer to the source data characters. When I worked at a lower level I had to deal with this constantly when folks doing mail merges had bad zip formats coming through, and as the “data guy” it all got referred to me to fix. Still, I think excel is great when used within certain confines. I use it for ad hoc purposes all of the time for simpler bits of work that are one-offs where it would take a fair bit longer to explore the data a bit and make some minimally presentable visuals & tables and some inline notes on analysis and interpretation.
- replygirl 4y ago
  > the default data type for cells is “general” and not explicitly numeric. To my thinking that should cause excel to defer to the source data characters you think zip codes are a more common use case than numbers?
  - ksala_ 4y ago
    I would expect it to leave my data alone unless told so. It’s one of the little things that drive me crazy with Excel the few times I use it, and every time I need to search how to make it behave because I don’t use it nearly enough to remember.
    - aardvarkr 4y ago
      I would expect it to store 01234 as a number unless explicitly told to store it as a string. This is exactly how it should work. I’m all for hating on excel but being upset because the software can’t read your mind and your eccentric edge case isn’t automatically picked up is a bit entitled
      - ksala_ 4y ago
        I think I want to do the opposite of reading my mind to be honest. The default cell type is generic, so just assume everything is a string and don’t do any parsing. Maybe show a tooltip to quickly change the cell type if you detect something that might be a number or a date. Probably it’s just different usage - when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, but simple to update and share with other people (even not technical folks) or to open a csv and quickly hide/filter some data.
        Karellen 4y ago
        > The default cell type is generic, so just assume everything is a string and don’t do any parsing. So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? That's... not why spreadsheets were invented. > when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, Then use a database program, not a spreadsheet! However, if there are no database programs that fit your needs, and you end up using a spreadsheet instead, don't complain that the spreadsheet acts like a spreadsheet instead of a database. It's not the tool's fault that you're the one using the wrong tool for the job.
- MonkeyMalarky 4y ago
  The leading +1 on international numbers is a pain too.
- bombcar 4y ago
  Zip codes are easy to fix. What’s painful is when a file has phone numbers and excel helpfully converts them all to scientific notation.
- jagged-chisel 4y ago
  I always prefix zipcodes with a single quote. It tells Excell “this is text” and the leading single quote does not render.
  - EForEndeavour 4y ago
    It's little things like parsing unmatched leading single quotation marks as meaningful non-errors that contributes the "hate" component of my love-hate relationship with Excel.
  - deleted 4y ago
    [deleted]

ineedasername 4y ago

And I still can’t believe that after multiple decades it removes the leading “0” for things like zip codes. It does this even though the default data type for cells is “general” and not explicitly numeric. To my thinking that should cause excel to defer to the source data characters. When I worked at a lower level I had to deal with this constantly when folks doing mail merges had bad zip formats coming through, and as the “data guy” it all got referred to me to fix. Still, I think excel is great when used within certain confines. I use it for ad hoc purposes all of the time for simpler bits of work that are one-offs where it would take a fair bit longer to explore the data a bit and make some minimally presentable visuals & tables and some inline notes on analysis and interpretation.

replygirl 4y ago
> the default data type for cells is “general” and not explicitly numeric. To my thinking that should cause excel to defer to the source data characters you think zip codes are a more common use case than numbers?
- ksala_ 4y ago
  I would expect it to leave my data alone unless told so. It’s one of the little things that drive me crazy with Excel the few times I use it, and every time I need to search how to make it behave because I don’t use it nearly enough to remember.
  - aardvarkr 4y ago
    I would expect it to store 01234 as a number unless explicitly told to store it as a string. This is exactly how it should work. I’m all for hating on excel but being upset because the software can’t read your mind and your eccentric edge case isn’t automatically picked up is a bit entitled
    - ksala_ 4y ago
      I think I want to do the opposite of reading my mind to be honest. The default cell type is generic, so just assume everything is a string and don’t do any parsing. Maybe show a tooltip to quickly change the cell type if you detect something that might be a number or a date. Probably it’s just different usage - when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, but simple to update and share with other people (even not technical folks) or to open a csv and quickly hide/filter some data.
      - Karellen 4y ago
        > The default cell type is generic, so just assume everything is a string and don’t do any parsing. So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? That's... not why spreadsheets were invented. > when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, Then use a database program, not a spreadsheet! However, if there are no database programs that fit your needs, and you end up using a spreadsheet instead, don't complain that the spreadsheet acts like a spreadsheet instead of a database. It's not the tool's fault that you're the one using the wrong tool for the job.
MonkeyMalarky 4y ago
The leading +1 on international numbers is a pain too.
bombcar 4y ago
Zip codes are easy to fix. What’s painful is when a file has phone numbers and excel helpfully converts them all to scientific notation.
jagged-chisel 4y ago
I always prefix zipcodes with a single quote. It tells Excell “this is text” and the leading single quote does not render.
- EForEndeavour 4y ago
  It's little things like parsing unmatched leading single quotation marks as meaningful non-errors that contributes the "hate" component of my love-hate relationship with Excel.
- deleted 4y ago
  [deleted]