Description: I was checking my arithmetic on the length of string needed to hold the decimal representation of a large number. I found Calc's view differed from mine. On investigation there is a jump in the result of len for large integers. Steps to Reproduce: 1.Create new spreadsheet 2.Format cell A1 as "number, -1235" (format code 0) 3.Paste the value 1801439850948200 into cell A1 4. Increase the width of column 1 until the whole number can be seen (not ###) 5. Insert the formula =len(a1) into cell B2 6. Observe the result 16 in B2. 7. Append a zero to the number in cell A1. 8. Observe the new length 20 in B2. Actual Results: 20 (see step 8 above) Expected Results: 17 Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:54.0) Gecko/20100101 Firefox/54.0
I think this is related to bug 96918. @Eike, could you please share your opinion on this?
I don't think it's related. The display shows the correct text in all cases. It looks as if the length is being calculated from the wrong representation of the string - for example the number 1E+016 has a strlen of 6, even when the cell whose length is being obtained contains 10000000000000000 (format code -1235)
Note that after step 7 above (appending a zero), going back to cell A1 the input line changed to 1.8014398509482E+016 which is the length calculated in B2 as LEN() always calculates the "raw" input number, not the formatted number. The reason is that with appending a zero digit the number runs into a magnitude where the double floating point loses precision, ie. it can not be distinguished between 18014398509482000 and 18014398509482001, for example. However, the display format for A1 forces the number to be displayed using a non-exponential notation, note that it will display 18014398509482000 also for 18014398509482009 ... The maximum representable integer is (2^53)-1 or 9007199254740991.
Btw, use =LEN(TEXT(A1,"0")) if you want the length of the ambiguous display string.
I could wish I hadn't started this... I also have access to MS Excel (Pro Plus, 2013) and that also has odd behaviour. But different... Excel gives len as 16,17,18,19,19,19... as the number grows, and len(text(a1,"0")) as 16,17,18,19,21,22,23. (no 20!) Which unfortunately means that the len(text workaround that works for Calc doesn't work for Excel :(