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??🤔

Apr 11, 2022 in Database 275 views

## 1 answer to this question.

Because the value in cell B1 may appear to be 5, but it is actually kept internally as 4.99999999999989 - and that number has 16 characters when evaluated as a string by LEN - =LEN(B1) returns 16. ().

You may observe this for yourself by pressing the "Increase Decimal" button several times: The display ultimately gets to the point where it has enough decimal places to indicate the underlying value (the LEN function ignores trailing zeroes - again, those are display zeroes not stored zeroes).

You may achieve the same result by typing 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`

• 23,220 points

## How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

need the row/column combinations marked with an ...READ MORE

## Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

## Hours and minutes difference between two times in Excel

Using the TEXT function in Excel is ...READ MORE

## Excel COUNTIF with multiple criteria and both row and column

Count Cells Based On Text Value Using ...READ MORE

## Get month and year from date cells Excel

The steps to modify the date format ...READ MORE

## Generating username from first name and last name in excel

How to automatically merge first and last ...READ MORE