Last digits are changed to zeros when you type long numbers in cells of Excel

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.

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

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.

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

1. Select all the affected cells, and then press Ctrl+1 to open the Format Cells dialog box.

2. On the Number tab, select Text from the Category list, and then select OK.

   

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:

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

However, if you type the number in the cell that is formatted as text, all the characters remain as you type them because Excel stores the number as text and not as a number.

Still need help? Go to Microsoft Community.