WTF is Wrong With Excel? It Changes My Numbers!

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows
In my spreadsheet, I am trying to write out my sixteen-digit account number of 8448200017875626 but Excel is chnaging that last 6 to a 0 and I cannot figure out why. I cleared all formating but it still does it.

What the heck is Excel doing to me?!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

stefankemp

Board Regular
Joined
Mar 11, 2010
Messages
136
The 16 digit number is too big for Excel to handle. Change the format of the cell to Text then enter it.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
If it's just an account number, format the cell to text.

Excel won't let you input numeric data with that many significant digits, so it converts anything after the 15th digit to zero.
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
usually a number that large you need to format the cell to text. I can't remember the why - I might see if I can found that for you
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
usually a number that large you need to format the cell to text. I can't remember the why - I might see if I can found that for you

It is because Excel uses a floating-point decimal. Chip Pearson's Article on this topic explains a LOT: from why it can only be precice to 15 digits, and also why some simple addition/subtractions can seem to error.
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

But I can format as a number and it accepts all the digits, but only chnages the last one.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Yes, that's the point.

If it's formated as number, you can key in as many digits as you like, but it will convert anything after the 15th digit to zero.

You're entering a 16 digit number, so it converts the 16th one to zero.

To get round this, format the cell as text.
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,480
Office Version
  1. 365
Platform
  1. Windows
I can't believe I did not know that already!

Thanks everyone!!
 

Forum statistics

Threads
1,141,015
Messages
5,703,746
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top