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>

00420170080213373	15
00520170080210373	15
00720170080210730	15
00820170080210730	15
00820170080210732	15
00920170080210730	15
00920170080210731	15
01020170080210730	16

<tbody>
</tbody><colgroup><col><col></colgroup>

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".