8 ms·
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 c
by 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 agoI 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 agoI 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 agoI 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.
- Dylan16807 4y ago> So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? If you didn't change anything else, just made A1 and B1 text cells, then adding them would still return 6912. > That's... not why spreadsheets were invented. It's decades later. Why they were invented is not the priority. > Then use a database program, not a spreadsheet! Databases only have a fraction of the capabilities of a spreadsheet. That's not a reasonable response.
- zrobotics 4y agoUmm, if you are treating them as text cells, then adding them would product 12345678 since you are concatenating strings. I just don't think there is any good solution for this, since it's effectively impossible to automatically distinguish numbers that the user wants to treat as strings (like a zip code) and numbers that should be treated as floats or ints.
- thethimble 4y agoI think if you explicitly type in a leading zero with five digits it seems reasonable that it should infer it’s a zip code.
- boosteri 4y agoMaybe, given the appropriate locale in the system settings.
- watwut 4y agoOh no, Jesus, please no. Nothing better then Excell file what behaves completely differently when moved to another computer, purely because of something like locale in system settings.
- Findecanor 4y agoFirst, you should not conflate the locale of the user or program with the locale of the (span in the) file. Second, how would you handle international addresses? For many businesses, a program that can't do that would be useless.
- replygirl 4y agoprogrammers who don't work with time be like
- pbhjpbhj 4y agoIt needs Clippy-ing: "recognise column as zip codes? always, now, not now, never, turn off format recognition".
- 6510 4y agoIf the column is going to be called something stupid like A, B or C clippy is going to have a hard time....
- Dylan16807 4y agoI think cases where I want to preserve the leading 0s vastly outnumber cases where I don't.
- MonkeyMalarky 4y agoThe leading +1 on international numbers is a pain too.
- bombcar 4y agoZip 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 agoI always prefix zipcodes with a single quote. It tells Excell “this is text” and the leading single quote does not render.
- EForEndeavour 4y agoIt'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]