Fuck you Excel, that’s a date, not $946,684,800
Honestly, the automatic datetime conversion is the worst part if you’re just trying to keep it text. It’s idiotic there isn’t a simple way to turn off that off. That’s not formatting, that’s actually changing the data in those cells which may not be what you want.
In case you’re not aware, the latest version of Excel absolutely DOES have that setting (mentioned elsewhere here in the comments). While it’s wild that it took so long, it’s now a solvable problem and everyone should know about it (and upgrade)!
It should be, and should always have been defaulting to the “dont fuck about with my data” setting.
Because there really aren’t too many scenarios where I want my barcodes abbreviated to scientific notation.
Also means one has to give up the license they bought years ago for Office 2019 and actually pay monthly now.
Or use Ohook
Who hook?
The office activation method from massgrave.dev
It should make me irrationally angry, but no, it’s a rational hatred. It burns even more intensely.
I’m not sure what version got this, but there’s a setting now where you can disable auto-conversion and it’s amazing.
https://mashable.com/article/microsoft-excel-disable-setting-auto-conversion-data-into-dates
Holy crap, that’s amazing - I can’t believe it would take this long! I presume straight up numbers are no longer affected, too, but I’m almost willing to trust it and try it out…
Yeah it stops converting numbers too. At my job we have a lot of ids that start with 0, and it was super annoying to have ‘000123’ turn into ‘123’, now it keeps it as text.
That might just be the top tool of the week, then!
CSV files having their leading zeros cut off fuck my entire world up.
Instead of just opening them with Excel, make a blank workbook or sheet, go to the data tab, import from text/csv. It’ll give you a bunch of menus and such where you can adnust how it reads the file and how it will format columns. Any column with leading zeros you want as text. It doesn’t fuck with “as text” columns.
The only caveat is that once you’ve imported the CSV that way, you’ll want to open the data sources tab and delete the connection to the csv file, otherwise changes in the original csv file will change shit in your spreadsheet, but only if excel feels like syncing it. Better to shut that off and do it yourself as needed.
Dealing with phone numbers and people using excel…
can you imagine a world without excel?
Nice barcode you have there. Be a shame if somebody took any leading zeroes off.
Nice Int64 you have there. Be a shame if somebody rounded it to 14 significant digits.
Import from the data tab, force all columns as text formatting. Excel won’t fuck with the formatting then.
#VALUE!But treats a random half of your numbers as text
That’s a good thing.
Like how several Genes were renamed because of excel https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates
Alt+h+v+v
Unrelated but alt+e+s+v is so ingrained into my muscle memory. I should really get a macro pad
Ctrl+Shift+V exists now which does the exact same thing.
Actually it’s a bit better. Excel always ignores alt codes for the first key press or two after switching windows.
https://www.visidata.org/ > excel for manipulation and navigation of data.
laughs in pola.rs
“Programmer humor” “excel”
Programmers who have to deal with data I/O and SQL regularly need to use a spreadsheet to examine, columnize and validate data manually, so that data structures are confirmed to be correct. Nobodys programming in Excel per se, but its still a ubiquitous tool that we use on the regular.
