SOLVED

Weird Number Change

%3CLINGO-SUB%20id%3D%22lingo-sub-1400789%22%20slang%3D%22en-US%22%3EWeird%20Number%20Change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1400789%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20explain%20this%20to%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormat%20a%20cell%20to%20number%20with%20zero%20decimal%20places.%26nbsp%3B%20Paste%26nbsp%3B%3CSPAN%3E8299400461135159%20into%20the%20cell.%26nbsp%3B%20Hit%20enter.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20number%20changes%20to%26nbsp%3B8299400461135150.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20know%20I%20can%20format%20the%20cell%20as%20text%20to%20show%20it%20correctly%20nut%20I%20want%20to%20know%20why%20it%20changes%20when%20it%20is%20formatted%20as%20a%20number.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20ideas%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1400789%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1400947%22%20slang%3D%22en-US%22%3ERe%3A%20Weird%20Number%20Change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1400947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F671554%22%20target%3D%22_blank%22%3E%40EStein9077%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsuperuser.com%2Fquestions%2F1041458%2Fwhat-is-largest-value-number-that-i-can-store-in-an-excel-vba-variable%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsuperuser.com%2Fquestions%2F1041458%2Fwhat-is-largest-value-number-that-i-can-store-in-an-excel-vba-variable%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20Google%20search%20turned%20that%20up.%2015%20digits%20is%20the%20max.%20above%20that%20it%20starts%20doing%20what%20you're%20witnessing.%20Now%2C%20%22WHY%20THAT%20limit...%3F%22%20is%20another%20question%2C%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1400963%22%20slang%3D%22en-US%22%3ERe%3A%20Weird%20Number%20Change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1400963%22%20slang%3D%22en-US%22%3EWHY%20THAT%20limit%3F%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20kidding.%20Thanks%20for%20the%20info.%20Makes%20as%20much%20sense%20as%20Excel%20not%20being%20able%20to%20sort%20IP%20addresses%20correctly.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Can someone explain this to me?

 

Format a cell to number with zero decimal places.  Paste 8299400461135159 into the cell.  Hit enter.

 

The number changes to 8299400461135150.

 

I know I can format the cell as text to show it correctly nut I want to know why it changes when it is formatted as a number.

 

Any ideas?

2 Replies
Highlighted
Best Response confirmed by EStein9077 (New Contributor)
Solution

@EStein9077 

https://superuser.com/questions/1041458/what-is-largest-value-number-that-i-can-store-in-an-excel-vb...

 

A Google search turned that up. 15 digits is the max. above that it starts doing what you're witnessing. Now, "WHY THAT limit...?" is another question, of course.

Highlighted
WHY THAT limit?

Just kidding. Thanks for the info. Makes as much sense as Excel not being able to sort IP addresses correctly.