Len(), counting?

[srizki]

I have the following number and LEN() formula,
As you can see that LEN(), counting 15 for all other and 16 for the last one, while they all 16 characters long.

<form id="form" onkeypress="javascript:return WebForm_FireDefaultButton(event, 'cphMain_btnSearch')" onsubmit="javascript:return WebForm_OnSubmit();" action="./InboundSearch.aspx?PageMode=Invoices" method="post">
</form>[TABLE="width: 188"]
<tbody>[TR]
[TD="align: right"]00420170080213373[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]00520170080210373[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]00720170080210730[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]00820170080210730[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]00820170080210732[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]00920170080210730[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]00920170080210731[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]01020170080210730[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]


Please advise what I am doing wrong.

Thanks

 

[Leith Ross]

Hello Sohail,

You have the numbers formatted to display leading zeroes. The LEN() function only returns the count of real digits in the number.

 

[srizki]

Thanks,
I am sure I had some other problem, I forgot, it was not that obvious.

 

[joeu2004]

The data on the left should be entered as text. Then LEN would work as desired. Well, maybe not: LEN would return 17 for all of them.

However, the 16-character "number" (identifier) is suspicious. Although that is with the range of 16-digit numbers that Excel can represent exactly (<= 2^53), you cannot only type zero as the last digit, because Excel truncates data entry after the 15th digit, replacing any others to the right with zeros.

 

[srizki]

Thank you very much, appreciated.

 

[joeu2004]

you cannot only type zero as the last digit, because Excel truncates data entry after the 15th digit, replacing any others to the right with zeros.

Aarrgghh!! That should say "you can only type zero as the last digit".