Microsoft Excel, Office 2003, Office 2007, Office 2010, Office 2013, Office Watch, Office XP / Microsoft Excel, Microsoft Office, Office 2002, Office 2003, Office 2007, Office 2010, Office 2013, Office XP /

Excel LEN anomaly for large numbers

by Office Watch 17 February 2013

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.

Excel LEN anomaly for large numbers

See Also