Copy this doc to your own workspace
The below table is completely set up to showcase the errors
First, consider that the column type of Lead time is Text:
Right click the DispName header cell and select Edit formula
You should see the following:
Note that the Lead time column type shown here is “123” which means Number. Not Text like we’ve seen before
Close the formula edit dialog
Consider the following rows:
These are showing DispName values calculated fine.
These are displaying incorrect values in DispName.
Seems that “2-3” and “7-12” are trying to treat themselves as formulas or kind of. At the same time, “40-45” from row #2 is not causing this error. I tried to change the first number in “2-3” all way up to 12 and the calculation breaks. Not afterwards.
It seems, the Lead time column type is auto detected based on the percentage of “number-like” values.
Changing the first/last cell value did not help.
Adding a dummy row with non-numeric values did not help.
Copying values to a fresh column and adjusting the formula did not help.
Different manipulations with pasting from clipboard did not help.
Adding a space or apostrophe to all cells did not help - it swallows that and keeps breaking.
The only way to fight it at scale I found so far is prepending all cells with a character like ` not tracing back to the Excel legacy. But that’s not looking good.
Thanks for consideration!
Column type auto-detect failure test
There are no rows in this table