10 ms·
That's pretty funny. I work for a company that puts 5 alphanumeric characters on physical devices to track and identify them. We are always putting those 5 char
by andrewfromx 4y ago
That's pretty funny. I work for a company that puts 5 alphanumeric characters on physical devices to track and identify them. We are always putting those 5 characters into various google sheets. And every so often one had an E in the middle of all numbers and google would convert by default to something like "1.10E+34" so we had to make SCIENTIFIC_NUMBER_REGEX = Regex("\\d+e\\d+") and stop making codes with E's in the middle.
- philliphaydon 4y agoWhy not just set the column to text?
- charcircuit 4y agoI assume avoiding any issues popping up from having to remember to do that is worth the reduction in state space. (10 + 23)^5 = 39,135,393 which is enough for company devices
- philliphaydon 4y agoI assume there’s a row or column that this applies to in their sheet. So I’m suggesting instead of having some regex applies to check the values. Just setting the entire row or column to text would resolve the issue.
- charcircuit 4y agoWhen he says "various google sheets" it sounds like new ones are occasionally created so you would have to do that each time a new one is created. It's extra friction which you can easily avoid.
- philliphaydon 4y agoBut he’s putting a regex into the sheet? Unless he has some tool to look at the spreadsheets in gdrive and applies the regex to them? Guess we lack info from the OP.
- egypturnash 4y agoThe regex goes into their UUID generation routine, to stop it from ever generating another UUID with an E in the middle.
- jwilk 4y agoWhy 23?
- ericbarrett 4y agoGuessing no I, L, or O to avoid confusion with numbers.
- Dylan16807 4y agoAt that point I feel like you should just use base32 instead of 33. For just removing digits-E-digits, that's 10000 patterns for each E position, of which there are 3. Since 36^5 is over 60 million, you're only removing a twentieth of a percent of values. Closer to a tenth of a percent if you remove those values from 33^5 or 32^5.
- charcircuit 4y agoIt's more useful to look at the total amount of values rather than how much the space gets reduced. If you have 10s of millions of values you can have several devices for even the largest employers that exist today.
- charcircuit 4y agoIt was I, O, and E.
- robocat 4y agoWhen generating human readable alpha codes, never include vowels. Avoid generating codes like FARKU, WANKR, USUCK etcetera.
- samwillis 4y agoThis 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 agoAnd 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?
- 4y ago
- cm2187 4y agoExcel is also super dangerous. All numbers are stored as double, which means that if you open a csv file that contains large int64 ids, they will likely be rounded. And then there are dates, and people who still think it is a good idea to store dates in a US or European format in a csv file rather than something unambiguous and universal (yyyy-mm-dd).
- happyopossum 4y agoThat doesn’t sound like a dengerous excel problem, it sounds like a sloppy ETL problem. Don’t import data blindly - check the date formats for example.
- exadrid 4y agoThat's nice in theory, but in practice people do it. 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...
- joshspankit 4y agoThat’s garbage. If I input a number in to a spreadsheet I expect that number to stay exactly as-is. That a Excel could introduce bugs due to doing floats behind the scenes.... Completely unacceptable.
- trelane 4y agomust be new here .. :)
- joshspankit 4y agoHahahaha I appreciate that comeback. Well played.
- Someone 4y agoMost programming languages don’t behave that way, either. Why would we have to hold Excel to higher standards? Now, that Excel stores binary floats, but tries to pretend to use decimal ones, that’s something I think we can blame it for. From section 2 of https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf: Some parentheses in Microsoft’s Excel 2000 spreadsheet possess uncanny powers: Values Excel 2000 Displays for Several Expressions Expression 1.23456789012345000E+00 <– Entered to help count digits V = 4/3 displays… 1.33333333333333000E+00 Does Excel carry 15 sig. dec.? W=V- 1 3.33333333333333000E-01 Whence comes the 15th 3 ? X = W*3 1.00000000000000000E+00 Where went all 15 of the 9s ? Y=X- 1 0.00000000000000000E+00 They all went away ! Z = Y*2^52 0.00000000000000000E+00 Really all gone. (4/3 - 1)*3 - 1 0.00000000000000000E+00 Yes, gone. ((4/3 - 1)*3 - 1) -2.22044604925031000E-16 (But not ENTIRELY gone !) ((4/3 - 1)*3 - 1)*2^52 -1.00000000000000000E+00 Excel’s arithmetic is weird.
- hhh 4y agoRaspberry pi serial numbers have a lot of zeros and when migrating device management solutions w/ a CSV set up in Excel we lost a lot of time from serials getting mangled like this. I just don’t use excel anymore.
- mapmeld 4y agoThis reminds me of a story of a geocoder (postal address search) which was found to frequently fail in NYC. When they debugged, they found addresses in east Manhattan (e.g. 100 E 61 St) were being converted to scientific notation.
- funstuff007 4y agoThere many CUSIPs (strings) that Excel with convert to numbers (scientific notation).
- fragmede 4y agoI you want to avoid that "auto correct" behavior, prepend the cell contents with '. Eg '1e4.
- beejiu 4y agoIn the place I work, builds used to fail if the commit short-sha is interpreted as a number in exponential notation (e.g. 67124e2). It happened surprisingly often!