Failed Test 1

Various adverse effects of 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:
image.png
Effect 1:
Right click the DispName header cell and select Edit formula
You should see the following:
image.png
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:
image.png
image.png
These are showing DispName values calculated fine.
Now consider rows:
image.png
image.png
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
0
DispName
Quote for
Lead time
Price
1
samples
: $50.00in 7 days
samples
7
$50.00
2
1500 pcs
: $21.45in 40-45 days
1500 pcs
40-45
$21.45
3
3000 pcs
: $20.70
3000 pcs
$20.70
4
5000 pcs
: $20.35
5000 pcs
$20.35
5
samples
: in 43864 days
samples
2-3
6
1500 pcs
: $24.34in 30+ days
1500 pcs
30+
$24.34
7
samples
: $4.00in 43894 days
samples
3-4
$4.00
8
1500 pcs
: $3.10in 7 days
1500 pcs
7
$3.10
9
1500 pcs
: $11.80in 7 days
1500 pcs
7
$11.80
10
3000 pcs
: $3.10in 10 days
3000 pcs
10
$3.10
11
5000 pcs
: $3.10in 15 days
5000 pcs
15
$3.10
12
samples
: $2.40in 15 days
samples
15
$2.40
13
1500 pcs
: $2.40in 15 days
1500 pcs
15
$2.40
14
3000 pcs
: $2.40in 15 days
3000 pcs
15
$2.40
15
5000 pcs
: $2.30in 15 days
5000 pcs
15
$2.30
16
samples
: $50.00
samples
$50.00
17
1500 pcs
: $27.82in 30 days
1500 pcs
30
$27.82
18
1500 pcs
: $22.00in 40 days
1500 pcs
40
$22.00
19
3000 pcs
: $20.76
3000 pcs
$20.76
20
5000 pcs
: $20.00
5000 pcs
$20.00
21
samples
: $50.00in 44024 days
samples
7-12
$50.00
22
1500 pcs
: $18.12in 30 days
1500 pcs
30
$18.12
23
3000 pcs
: $16.87
3000 pcs
$16.87
24
5000 pcs
: $15.34
5000 pcs
$15.34
25
samples
: $150.00in 7 days
samples
7
$150.00
26
1500 pcs
: $13.93in 40-45 days
1500 pcs
40-45
$13.93
27
samples
: $5.70in 7 days
samples
7
$5.70
28
1500 pcs
: $5.70in 7 days
1500 pcs
7
$5.70
29
3000 pcs
: $5.70in 7 days
3000 pcs
7
$5.70
30
5000 pcs
: $5.70in 7 days
5000 pcs
7
$5.70
31
1500 pcs
: $12.50in 45 days
1500 pcs
45
$12.50
32
samples
: in 20 days
samples
20
33
1500 pcs
: $27.00in 20 days
1500 pcs
20
$27.00
34
3000 pcs
: $26.00
3000 pcs
$26.00
35
5000 pcs
: $24.50
5000 pcs
$24.50
36
samples
: $50.00in 44022 days
samples
7-10
$50.00
37
1500 pcs
: $36.50in 30-40 days
1500 pcs
30-40
$36.50
38
1500 pcs
: $37.27in 30-40 days
1500 pcs
30-40
$37.27
39
samples
: $30.00in 43897 days
samples
3-7
$30.00
40
1500 pcs
: $30.62in 44129 days
1500 pcs
10-25
$30.62
41
3000 pcs
: $29.23
3000 pcs
$29.23
42
5000 pcs
: $27.84
5000 pcs
$27.84
43
samples
: $10.00in 44022 days
samples
7-10
$10.00
44
1500 pcs
: $20.83in 40 days
1500 pcs
40
$20.83
45
3000 pcs
: $20.49
3000 pcs
$20.49
46
5000 pcs
: $20.21
5000 pcs
$20.21
47
samples
: $30.00in 44022 days
samples
7-10
$30.00
48
1500 pcs
: in 35 days
1500 pcs
35
49
samples
: $200.00in 20 days
samples
20
$200.00
50
1500 pcs
: $13.00in 20 days
1500 pcs
20
$13.00
51
3000 pcs
: $13.00in 20 days
3000 pcs
20
$13.00
52
5000 pcs
: $12.50in 20 days
5000 pcs
20
$12.50
53
:
There are no rows in this table

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.