Excel LEN anomaly for large numbers

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

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.

Large numbers with LEN function image from Excel LEN anomaly for large numbers at Office-Watch.com

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.

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.