Excel LEN anomaly for large numbers
A little Excel quirk to mention using LEN() with large numbers.
A little Excel quirk to mention. For as long as we can remember you could get the number of digits in a number by simply using =LEN() even though the input is a number, not text.
But have a look what happens for the larger numbers. LEN() returns either 19 or 20 even though the number is clearly a lot longer.
You need to convert the value explicitly to text then get the length ie =LEN(Text(value)) .
That’s good practice anyway. LEN() can return unexpected results unless you’re specific. For example using LEN on a date cell will return the length of the date number used by Excel.
See Also
Want More?
Office Watch has the latest news and tips about Microsoft Office. Delivered once a week.
We never share your email address with anyone - never have, never will. Privacy Policy.