When I have numbers longer than 20 characters the LEN() function in excel returns 5.
I've tried this on my desktop and my mobile and get the same result.
When I use "Evaluate Formula" it shows it going from "LEN(100000000000000000000)"(20 zeros) to "5".
In the case of 21 zeros it formats the number as scientific so it goes from "LEN(1E+21)" to "5".
So I guess that at 21 characters excel stores numbers with the scientific notation and LEN() calculates the length of that.
The anomaly of exactly 20 zeros still showing as "100000000000000000000" in "Evaluate Formula" is just a weird case?
Is there a way make the LEN() function work with long numbers or is there an alternative formula I should use?
Below is the table of data I was using to test this.
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Input</th><th>Len output</th><th>formula</th><th>input formatting</th><th>actual length (dp is just formatting not actually part of the number)</th></tr></thead><tbody>
<tr><td>10000000000000000000.0</td><td>20</td><td>=len(A2)</td><td>number 1dp </td><td>20</td></tr>
<tr><td>1000000000000000000000</td><td>5</td><td>=len(A3)</td><td>number no dp</td><td>22</td></tr>
<tr><td>10000000000000000000</td><td>20</td><td>=len(A4)</td><td>Text</td><td>20</td></tr>
<tr><td>100000000000000000000</td><td>21</td><td>=len(A5)</td><td>Text</td><td>21</td></tr>
<tr><td>100000000000000000000.0</td><td>5</td><td>=len(A6)</td><td>number 1dp </td><td>21</td></tr>
<tr><td>100000000000000000000.00</td><td>5</td><td>=len(A7)</td><td>number 2dp</td><td>21</td></tr>
<tr><td>10000000000000000000.00</td><td>20</td><td>=len(A8)</td><td>number 2dp</td><td>20</td></tr>
<tr><td>1E+19</td><td>20</td><td>=len(A9)</td><td>scientific</td><td>20</td></tr>
<tr><td>1E+20</td><td>5</td><td>=len(A10)</td><td>scientific</td><td>21</td></tr>
</tbody></table>
