Back
Excel

When a Number is Not a Number

If you use Excel to track identification numbers (serial numbers, tracking numbers, etc.) you may find that if the IDs have more than 15 digits, Excel loses the digits after the 15th and makes them all zero.  In other words, type “123456789123456789″ into a cell and Excel will show you “123456789123456000“.  What’s going on?  Is this a bug?!  Nope.  You’ve hit an Excel limit with how large of a number Excel can handle when performing math operations on a number.

But in this case, we don’t care about performing math on things like tracking numbers.  The solution?  Don’t make it a number at all.  Select the cells where you will be entering these identification numbers, right-click, select Format Cells and choose the Text option in the Number tab.  This tells Excel to treat the number like normal alphanumeric text, which means they will display exactly as you enetered them, but you can’t really use the value represented in the string as inputs to formulas (I’m over simplifying here a bit – those formula savvy among us know there are ways around this, but that’s beyond the point of this discussion).

There’s a shortcut too, if you type an apostrophe before entering that long tracking number, that’s a signal to Excel to treat the value as Text.