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.
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)
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.
That did not work