SOLVED
Home

Problem with Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-312628%22%20slang%3D%22en-US%22%3EProblem%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312628%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20enter%20a%2016%20digit%20number%2C%20with%20the%20trailing%20decimal%20point%20set%20to%20zero%2C%20the%2016th%20digit%20automatically%20changes%20to%20a%20zero%20regardless%20of%20the%20last%20number%20entered.%20This%20is%20what%20I%20mean%3A%26nbsp%3B%3C%2FP%3E%3CP%3Eenter%3A%20xxxxxxxxxxxxxxx8.%20Excel%20records%20xxxxxxxxxxxxxxx0.%20I've%20tried%20general%2C%20number%20and%20nothing%20helps.%20This%20only%20happens%20with%2016%20digit%20numbers%2C%20not%20with%201%26nbsp%3Bto%2015%20digit%20numbers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-312628%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314401%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314401%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20it's%20an%20account%20number%2C%20I%20assume%20you%20don't%20need%20to%20store%20it%20as%20a%20number.%20So%2C%20start%20with%20a%20single%20quote%20and%20you%20can%20enter%20as%20many%20digits%20as%20you%20like.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-314343%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-314343%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20reason%20for%20the%2016%20digit%20number%20is%3A%20it%20is%20an%20account%20number%2C%20so%20every%20digit%20is%20important.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312647%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312647%22%20slang%3D%22en-US%22%3E%3CP%3EAnd%20to%20work%20with%20such%20strings%20as%20with%20numbers%2C%20e.g.%20SUM%2C%20etc.%2C%20you%20may%20use%20third-party%20add-ins%20like%26nbsp%3B%3CFONT%3E%3CA%20href%3D%22http%3A%2F%2Fwww.excel-ticker.com%2Fcalculation-of-very-large-numbers-in-excel-part-5-add-in%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.excel-ticker.com%2Fcalculation-of-very-large-numbers-in-excel-part-5-add-in%2F%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312632%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312632%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20can%20only%20store%20up%20to%2015%20significant%20digits%20in%20a%20number.%20Anything%20beyond%20that%20will%20be%20turned%20into%20zeros.%20Do%20you%20really%20need%20this%20as%20a%20number%3F%20I%20don't%20even%20know%20the%20word%20for%20a%20number%20that%20large%2C%20and%20if%20you%20have%20such%20large%20numbers%2C%20surely%20the%20singles%20at%20the%20end%20are%20fairly%20insignificant%2C%20given%20the%20magnitude.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%2C%20however%2C%20these%20numbers%20are%20not%20used%20in%20any%20calculations%2C%20you%20can%20simply%20precede%20them%20with%20a%20single%20quote%2C%20then%20Excel%20treats%20them%20as%20text%20and%20you%20can%20have%20a%20lot%20more%20that%2015%20digits.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20461px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F65689iAFB958A6D7A0EF6E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22digits.png%22%20title%3D%22digits.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E123%2C456%2C789%2C012%2C345%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-312630%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20with%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-312630%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20by%20design%2C%20only%2015%20digits%20are%20supported.%20If%20you%20need%20more%20use%20texts%20instead%20of%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mr_Ron
New Contributor

When I enter a 16 digit number, with the trailing decimal point set to zero, the 16th digit automatically changes to a zero regardless of the last number entered. This is what I mean: 

enter: xxxxxxxxxxxxxxx8. Excel records xxxxxxxxxxxxxxx0. I've tried general, number and nothing helps. This only happens with 16 digit numbers, not with 1 to 15 digit numbers. 

5 Replies

That's by design, only 15 digits are supported. If you need more use texts instead of numbers.

Solution

Hello,

 

Excel can only store up to 15 significant digits in a number. Anything beyond that will be turned into zeros. Do you really need this as a number? I don't even know the word for a number that large, and if you have such large numbers, surely the singles at the end are fairly insignificant, given the magnitude.

 

If, however, these numbers are not used in any calculations, you can simply precede them with a single quote, then Excel treats them as text and you can have a lot more that 15 digits.

 

digits.png

 

123,456,789,012,345

And to work with such strings as with numbers, e.g. SUM, etc., you may use third-party add-ins like http://www.excel-ticker.com/calculation-of-very-large-numbers-in-excel-part-5-add-in/

The reason for the 16 digit number is: it is an account number, so every digit is important.

If it's an account number, I assume you don't need to store it as a number. So, start with a single quote and you can enter as many digits as you like.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies