Gallery

Why Excel won't show more than 15 digits

Posted on 31 Aug 2010 at 11:08

Simon Jones helps a reader crack a problem with long digits in Excel

I recently received this heart-rending plea from a reader who’d obviously been struggling for some time and had reached the end of their tether: “I have a list of products that we sell, and one of the fields is the UPC – this field can be up to 18 digits long. When I add beyond 15 digits, Excel is auto-rounding the last three to 000. If I set this field to be text it adds the +E11 at the end of the string. It doesn’t matter if I have the column width longer than three times the normal length. How can I fix this?

“I just wish Excel would do what it’s told to without having to add things. For example, if the cell format is “TEXT” then don’t format anything in it! It appears I need to use Access just to make some small changes to a CSV file – to use Access as a spreadsheet instead of a spreadsheet. I have to wonder if OpenOffice does the same.”

It doesn’t matter what size the number is or where the decimal point is placed, Excel will store only its first 15 significant digits and discard all the rest

There are some fundamental limitations to any software product, and Excel is no exception. Excel can hold numbers to only 15 significant figures because it uses IEEE Floating Point Maths, which dictates how numbers – which can be as large as 1.79769313486231E+308 or as small as 2.229E-308 – are stored without making the workbook consume gigabytes of space and take hours to recalculate. This limitation is clearly set out in the Excel help text.

Note that “significant figures” aren’t the same as “decimal places”; it doesn’t matter what size the number is or where the decimal point is placed, Excel will store only its first 15 significant digits and discard all the rest.

UPC stands for Universal Product Code, which is actually a symbolic or code name composed of digits, rather than being a mathematical number.

Adding or subtracting two UPCs doesn’t make any sense and doesn’t lead to another valid UPC code, even though each code is composed entirely of digits. If you’re not doing any mathematics on your data, as is the case with UPCs, then you can force Excel to store them as text by typing an apostrophe before you type the first digit. This tells Excel that what you’re typing isn’t a number, even though it looks like one, and that it should be stored as text.

Alternatively, if you format a cell as Text and then type a long string of digits into it, Excel does exactly what you’d expect and retains all its digits because it treats them as text characters, not numeric digits. However, if you forget to format the cell as Text before you type the digits, or if you don’t type a leading apostrophe, then Excel will treat the digits as a number, and once data has been entered it will be truncated to 15 significant figures and you can’t get the lost digits back.

What’s more, importing data from a CSV file is quite different from typing the data directly into Excel, because a CSV file doesn’t contain any clues as to the format of the data in its fields. When you just double-click on a CSV file to open it in Excel, or when you use Excel’s File | Open dialog, Excel will guess that any fields that contain all digits are to be treated as numbers, which is more often than not what’s wanted. It doesn’t matter whether or not those fields are enclosed in quotation marks: if they’re all digits, then Excel simply assumes they’re numbers.

However, if instead of just opening the CSV file you click on Data | Get External Data | From Text, then you’ll be given control over the import process and can choose the format for each column of data, so you can tell Excel that your long strings of digits should be treated as text, not as numbers.

OpenOffice makes exactly the same assumptions: whenever you type or import data that looks like a number, both OpenOffice and Excel will treat it as a number by default, and if you want to override that perfectly reasonable assumption then you’ll have to give the application some help.