I want to get the length of a formula result, but it returns incorrectly.
For example, I put 1005 in the cell A1, put the formula =(A1/1000-FLOOR(A1/1000,1))*1000 into B1 and the result is 5, that's correct. But when I put the formula =LEN(B1) into the cell C1, the result is 16? Even worse, the formula =REPT(0,3-LEN(TEXT(B1,"0")))&(B1) or =REPT(0,3-LEN(TEXT((A1/1000-FLOOR(A1/1000,1))*1000,"0")))&((A1/1000-FLOOR(A1/1000,1))*1000) returns 004.99999999999989??
The reason =LEN(B1) returns 16 is because the value in cell B1 may be displayed as 5, but it is actually stored internally as 4.99999999999989 - and that number has 16 characters in it, when evaluated as a string by LEN().
You can see this for yourself by repeatedly clicking on the "Increase Decimal" button:
Here, the display eventually has enough decimal places to reflect the underlying value (the LEN function ignores trailing zeroes - again, those are display zeroes not stored zeroes).
You can also see the same thing if you simply enter the following formula into a cell:
=1.005-1
This actually has 19 characters in it, not 16, because we have not multiplied by 1,000:
0.00499999999999989
The loss of precision from your displayed 5 to the actual stored 4.99999999999989 is indeed because of how floating point math works, as mentioned by BigBen.
See also Is floating point math broken? for a broader discussion.