Add a ' prefix to them so they are treated as text. We've had problems with many-digit integers being converted to floating point approximations instead.

How did people keep up with that? Is there really no competitor who gets this right? These unexpected conversions must have cost billions in problems they created or fixing time they needed. And now after so many years they just remove some types of auto conversion and leave the others? Sounds like some kind of Stockholm syndrome...

Not just Excel, this affects a LOT of dynamically typed programming environments. JavaScript is rife with this sort of thing.

I don't think that's a fair comparison. Here we talk about a tool that fucks up your data once you load it. With javascript there is no such thing. There are some auto type conversions when passing a wrong type into a function that can yield unexpected results. But I would argue this is different as it's an environment for power users only, and there is types at play that should make you as a programmer aware.

E.g.

1 + "hello" = "1hello"

1 - "hello" = NaN

JSON parsing treats non-quoted numerals as a Number. That alone can result in some truncation.

Though, in JSON, anything that isn't a number should be quoted?

If the JSON writer is writing according to spec

I don't understand how this is at all a similar issue. If you want a string in JSON, then you must quote it.

https://www.json.org/json-en.html

And if you want a string in Excel you must apostrophe-prefix it. If you think “serial number” == number, telephone number == number, then try to use the number handling, you get problems because “numbers” don’t have leading zeros or spaces or parens or hashes or plus symbols.

I would like to store, then retrieve exactly 1234567890123456789 apples instead of 1.070816993713379 × 2⁶⁰ = 1234567939550609408. Unfortunately due to IEEE-754 conversions I can only do this up to 7-8 digits or so. This is a number, just not one that can be represented as a Number without loss of precision towards the end.

that's more apples than we can fit on the planet. For such special tasks I would think it's ok to need special tools

Dynamic typing is orthogonal to implicit type conversion, though admittedly dynamic environments do it more often. Watch out for (void*) in C or accidentally inferred union types in TypeScript.

// Is there really no competitor who gets this right?

Clearly excel has offered value far in excess of this annoyance. The competitors such that they existed aside from google sheets clearly fail to deliver sufficient value even in hypothetical absence of this bug.

You say "clearly" twice, but it's not clear at all to me that this is the case. Can you elaborate what makes it clear that no other competitor exists who can provide similar value?

And isn't LibreOffice Calc closer to Excel than Google Sheets in terms of features?

I use libre at home and excel at work. Having worked in various departments of a somewhat regulated industry, I can say that the value of Excel comes from familiarity and 'not having to retrain everyone'. It seems silly to people like me, but I personally experienced a person unable to complete their daily routine, because version change moved from one menu to another.

I guess my point it is: it is not features. It is humans. MS cracked the code on that one. Get people familiar with their stuff and the rest will follow.

> person unable to complete their daily routine, because version change moved from one menu to another.

> I guess my point it is: it is not features.

Discoverability of commands, customization, as well as the stability of UI are all features. As would be "full UI compatibility with Excel sans stupid bugs like data conversion"

I am a simple man and I use “people use it” as a proxy for value. LibreOffice is available everywhere excel runs, for free, and yet …

The main reason people continue using it is due to the network effect (xlsx files are considered shareable) and the fact that people are trained on its features and interface. That does make it valuable, but that doesn't mean it is more usable than competitors.

The USA got plenty of value from opioids then

All spreadsheets are bad. There are no good spreadsheets.

I suspect Excel's primary value at this point is its entrenched monopoly position.

I don't think it has anything of the kind. "Office" is no longer a must for any home user, and most businesses starting today have a clear choice between (at the very least) Office and gSuite.

I haven't had Excel installed on my work laptop for the current or previous job. Though if I didn't have access to Sheets and had to use Libre by default, I'd petition for Excel in a second...

Once you’ve done this read in the data into Python or JavaScript and upload it to a backend.

Fun.

That's stupid and when I export to csv it has a '. Excel should just stop fucking with shit

Adding a prefix breaks any other process that might read that data.

Set the column/cell format to “text” and it will retain what you typed.

That doesn't work