Last digits are changed to zeros when you type long numbers in cells of Excel – ShapeNetSoftware Knowledge Base

Applies to: Excel 2013, Excel 2010, Microsoft Office Excel 2007, Microsoft Office Excel 2003

Symptoms

When you type a number that contains more than 15 digits in a cell in Microsoft Excel, Excel changes any digits past the fifteenth place to zeros. For example, you type a credit card ID number in the following format:

####-####-####-####

In this situation, Excel changes the last digit to a zero.

Cause

Excel follows the IEEE 754 specification for how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number and changes digits after the fifteenth place to zeros

Workaround

Add a quotation mark

To prevents digits from being changed to zero, type a single quotation mark before you enter the number.

To do this, select a blank cell, type a single quotation mark ('), and then enter the number. All digits are displayed in the cell.

Type a single quotation mark and enter the number.

Format the cells

To avoid having to type a quotation mark in every affected cell, you can format the cells as text before you enter any data.

Select all the affected cells, and then press Ctrl+1 to open the Format Cells dialog box.
On the Number tab, select Text from the Category list, and then select OK.

More information

This behavior occurs only if the cell is formatted as Number, and the number that is entered exceeds 15 digits. For cells that are formatted as text, you can type up to 32,767 characters. Excel displays up to 1,024 characters on the worksheet.

Because custom number formats are designed to work primarily with numbers, you cannot create a custom number format that stores more than 15 digits. For example, you cannot use the following format to store a 16-character credit card ID as a number: