Excel Changes Last Digit to Zero

When you paste or type a number with a certain length into Excel, Excel changes last digit to zero. This happens when the number is longer than 15 digits. However, there is an easy way to overcome this issue.

Here is a number with 16 digits inputted into cell A1:

 

When you press Enter button, it will be displayed as below:

 

It is not only the display that changes, excel changes last digit to zero too.

 

When your Excel changes last digit to zero, easiest and fastest solution is:

Select the cells that will host these numbers and format them as text as shown below.

 

Paste or input your long numbers.

 

Now your number is stored as text. You are free from digit restriction and your number is displayed as it is. An exclamation mark will pop up next to the cell whenever you select it. That is caused by storing a number as text data. Just ignore it and you will be fine. If you convert those values to number, it reverts back to previous state and Excel changes last digit to zero.

You may also like...

2 Responses

  1. Ganesh Karra says:

    Good
    But you cannot sum the contents.

    Example:
    As Number in Cell (A2): 12345678901
    As Text in Cell (A3) : 123456789012345
    total in Cell (A4) : 123469134691246

    Formula:
    =SUMPRODUCT((A2:A3)*1)

  2. melih says:

    Formula:
    =TEXT(A2+A3,”#”)
    should work for this example.

    But this article is more about displaying long numbers correctly in Excel.
    If you really need to work with that long numbers, you can try to divide all by 10.000 (or some other divisor) to be able to do it without data loss.

Leave a Reply

Your email address will not be published. Required fields are marked *