[SNvD7vEJ]

Why is the auto-convert 'features' in Excel not opt-in?

When Excel encounters the first cell in a new sheet that it thinks should be auto-converted, why does it not ask if that is desirable for that sheet?

Like: "Do you want Excel to interpret and auto-convert all strings with format <X> into the type <Y> in this sheet?"

At least for conversions where the original data is lost.

[JumpCrisscross]

I don't think Excel ever destroys the underlying data. It simply layers formatting over it. One can override by specifying custom formatting, or opt out by selecting all cells and setting their format to text.

[wongarsu]

Once the data is in the cell, changing cell formatting is a nondestructive visual layer. But while inputting data, cell formatting changes the interpretation of the input, which is destructive.

The article describes in some detail how inputting SEPT2 in a cell with default formatting displays 9/2/2016, but is stored as 42615 (which you get if you later change the cell to text formatting).

[jpindar]

If you enter a string whose first character is a "0" and Excel decides to change it to a number, the "0" is lost.

[ethan_g]

Commas are an exception to that. If you paste in "10,100", it will be interpreted as a number, and you lose the comma.