Just Excel Things
Wednesday, 12 Mar 2025
Exclusive insights, data, and analysis for financial market experts.
Search
Have an existing account?
Sign In
And that one time when I actually needed it to parse the date in slightly unusual format – it failed. Excel being excel at what it does…
Every single time.
We have personal registration ID numbers in Denmark. In Excel there is even a cell format to account for that. Some of these numbers might start with 0, as they relate to dates.
Excel does a 50/50 of actually having the 0 in the cell, while not actually having the 0 in the cell. So if you import it to a software for data handling, such as R, you end up with numbers where the first 0 is omitted.
It’s so frustrating.
I spent 2 hours going bonkers over this, finally chose text format so it doesn’t edit it at all… Kinda sucks.
Edit: Going bonkers on different formats on how to fix it and preserve my cell values that I had already entered. That day I must have written all the data at least 5 times.
Excel neither likes me pasting in EAN’s or other product numbers at work. It’ll just take that as a number but then show it in the scientific notation. Sure, best intentions, but just… fuck.
Me: 12/1/23
Excel: I’m gonna default this as a string for now.
Me: Ok, but this is clearly a date, and I’m going to force you to format it as such.
Excel: #####################################################################
Hate this. Ctrl+1 and set format to general or number usually fixes it.
Wait until you start playing with Power pivot ;3
I fucking hate excel.
We have to parse .tsv files and the most common reason they fail is because someone opened the file in excel to validate it, and saved it. Excel will always add extra tabs to the end of each line on a save and cause the files to fail validation during the parsing process.
I don’t use MS Office. I use LibreOffice.
I just went down this rabbit hole, using conditionals with dates in Excel is an absolute chore. Once you get it though… This issue probably keeps a million people employed every year.
Scanning serial numbers into a spreadsheet. 20 go fine. Then suddenly it decides bo! The new ones need a format that is irreversible and fucks all your data!
I’ve never seen Excel convert a decimal to a date unless the cell (or column) was also set to the date type.
You can deactivate the automatic date conversion in the settings.
On Google drive I keep a spreadsheet of my working hours. Same problem. In addition for some reason if I enter 12.5 on my phone it doesn’t recognise it as a number for my ‘total’ formula.
Oh, yes! I hate this so much!
Microsoft is the OG of horrible Auto Correct
Goddammit this is way too true
yeah but you can use it to text nelly
I have to deal with a lot of spreadsheets and csvs in excel involving mobile device SIMs and IMEIs, which are typically 15-18 digit long numbers. I cannot tell you how much of an ass pain it is to have excel constantly convert those into scientific notation, because once you save a document with those formatted like that then you forever lose part of that string of numbers.
just put a ‘ in front of what you want to type but not auto format
Ctr+Shift+1
As long as you set the cell or column format properly *before* you enter the data, Excel won’t do this. Also “paste special -> text” is your friend. Set it as default and you’ll be much less frustrated.
For being one of the largest software developers on the planet, Microsoft sure is awful at designing software.
Yes, Excel, this column of alphanumeric product id numbers that has “product_id” in the header contains two random dates in the form of 8-digit numbers.
Real ones suffer the CHAR(160) mystery.
The worst thing is, it doesn’t just misinterpret the data, but actually changes it.
How is it that this wildly unpopular bug has never been fixed through so many versions?
THIS is where we could use predictive AI! OMG, it’s so obvious what you’re trying to do and it does stuff like this.
Fun fact: parts of the human genome were renamed specifically because Excel liked to interpret their names as dates and kept screwing up the formatting of their spreadsheets.
Put an ‘ before your numbers, and it won’t change to a random date. I frequently have to input things like ’12/XX and it was quite annoying, but ya.. apostrophe before.
Space first then nr.
Excel gives it a good shot but it’s inevitable it won’t properly do what you want it to all the time because everyone’s expectations and needs are different and Excel has to cater to the common denominator.
It’s best to use the Format Cell feature to explicitly define that data type that is going in each cell. Then Excel doesn’t have to try to read your mind.
I had to use a CSV to import data into ADP for a client. This exact situation drove me nuts. With a CSV, you can’t use the “text” format trick so I ended up having to use Notepad++ to edit the damn CSV. Frustrating!
If you want a quick fix next time put a ‘ in front of the numbers. It is annoying though.
Excel is like the dumbest kid in the room who tries so hard to be a smart ass.
my favorite one is putting a 8 digit number that starts with a 3, so excel just puts 3e+4numbers
Honestly, the only time I have had this issue is when I go from CSV to XLSX.