Explore

Failed Test 1

Various adverse effects of implicit column type auto-detection

Dmitri Silaev

SYNOPSIS:

Problems with implicit column type auto-detection⁠

⁠

Steps to reproduce:

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:

⁠

Effect 1:

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

Effect 2:

Consider the following rows:

⁠

These are showing DispName values calculated fine.

Now consider rows:

⁠

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.

Workaround:

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

Column type auto-detect failure test

There are no rows in this table

⁠

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.