Excel LEN anomaly for large numbers

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address