Why is Excel truncating my 16-digit numbers?

If I type in a 16 digit number (format: number, no decimals) it changes the number on me. Example: 1234567812345678 changes the view to 1234567812345670.

If I type it in as a general format it changes the numbers above so it displays 1.23457E+15 but if you click on the cell, the display shows the last digit as a 0 instead of an 8 once again.

I opened the file on a different computer and same issue now with it. I have changed the auto correction and auto formatting all to no avail. Help!

Going Crazy

Posted 2012-06-16T20:25:07.677

Reputation: 151

Is it really a 16 digit number? Or is it really 16 digits? – David Schwartz – 2014-10-16T07:36:49.907

6@PleaseStand I think you are jumping to conclusions real fast here. I also needed this post, and the numbers are comment identifiers on a social media network; exactly 16 digits. Maybe stick to the question at hand instead of preaching that the data should not go into the format. – Tommy – 2015-03-30T15:13:13.823

Adding more than 15 digits in Excel – phuclv – 2017-03-03T05:42:47.990

Answers

It is a limitation placed on Excel by Microsoft. Each cell can have a maximum 15 digits of precision.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

enter image description here

wbeard52

Posted 2012-06-16T20:25:07.677

Reputation: 3 149

+1 "This is caused by the IEEE specification of storing only 15 significant digits of precision." (Source: http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B78113)

– Ellesa – 2012-06-16T22:44:57.493

Not the answer I wanted, but I appreciate the reason for why it was driving me crazy. Thanks for taking the time to answer! – Going Crazy – 2012-06-16T23:14:31.330

In addition to wbeard52's answer, here are some workarounds:

Entering the numbers as text. You may type a ' before each number, or change the cell's number format to Text.
There's an addin called Xnumbers which "performs multi-precision floating point arithmetic from 1 up to 250 significant digits."

Ellesa

Posted 2012-06-16T20:25:07.677

Reputation: 9 729

you can copy and paste a column of 16 digit numbers into excel like this:

Open a new excel document.
Leave it blank then click FILE / SAVE AS.
From the type box, select "text (tab delimited file)" option.
Format the column where you want to paste the 16 digit numbers as "text".
Copy your 16 digit numbers from the original document (word, email, where ever they are) and paste them into the column in the tab delimited file that you just formatted as text. Save it again.
You will see an error warning. Select all of your 16 digit numbers and select "ignore error" from the list of error handling options.
Keep the file as a text file or save it as an excel file, it works either way. You might see the error warning again, just select all and ignore error again.

vicki petry

Posted 2012-06-16T20:25:07.677

Reputation: 21