SOLVED

XL last digit keeps going to a zero in a 16 digit string

%3CLINGO-SUB%20id%3D%22lingo-sub-2590591%22%20slang%3D%22en-US%22%3EXL%20last%20digit%20keeps%20going%20to%20a%20zero%20in%20a%2016%20digit%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2590591%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%2016%20digit%20number%20in%20a%20cell%20in%20xl%20and%20it%20keeps%20going%20to%20zero%20when%20entered.%3C%2FP%3E%3CP%3ESo%20the%20last%20four%20numbers%20are%206167%20and%20the%20cell%20keep%20returning%20to%206160.%3C%2FP%3E%3CP%3EWhen%20I%20split%20the%20number%20into%20two%20cells%20each%20with%208%20numbers%20they%20stay%20the%20same.%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20cell%20size%20and%20the%20decimal%20point%20increasing%20as%20I%20have%20seen%20on%20forums%20but%20these%20do%20not%20work.%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20format%20the%20cell%20in%20different%20ways%20but%20this%20does%20not%20work.%3C%2FP%3E%3CP%3EI%20can%20only%20assume%20that%20it%20is%20the%20number%20of%20digits%20%2216%22%20has%20something%20to%20do%20with%20it%3F%20can%20I%20turn%20this%20function%20off%20in%20some%20way%20as%20I%20want%20a%207%20at%20the%20end%20of%20the%20number%20and%20not%20zero%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EDarren%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2590591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2590630%22%20slang%3D%22en-US%22%3ERe%3A%20XL%20last%20digit%20keeps%20going%20to%20a%20zero%20in%20a%2016%20digit%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2590630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1113613%22%20target%3D%22_blank%22%3E%40Darren_Stockport1460%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20an%20intrinsic%20limitation%20of%20Excel%20-%20see%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fmicrosoft-365%2Fblog%2F2008%2F04%2F10%2Funderstanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EUnderstanding%20Floating%20Point%20Precision%2C%20aka%20%E2%80%9CWhy%20does%20Excel%20Give%20Me%20Seemingly%20Wrong%20Answers%3F%E2%80%9D%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20format%20the%20cell%20as%20Text%2C%20or%20prefix%20the%20value%20with%20an%20apostrophe%20'.%20But%20if%20you%20perform%20calculations%20with%20the%20value%2C%20the%20result%20won't%20be%20precise.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I have a 16 digit number in a cell in xl and it keeps going to zero when entered.

So the last four numbers are 6167 and the cell keep returning to 6160.

When I split the number into two cells each with 8 numbers they stay the same.

I have tried the cell size and the decimal point increasing as I have seen on forums but these do not work.

I have tried to format the cell in different ways but this does not work.

I can only assume that it is the number of digits "16" has something to do with it? can I turn this function off in some way as I want a 7 at the end of the number and not zero?

 

Thanks

Darren

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Darren_Stockport1460 

This is an intrinsic limitation of Excel - see Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” 

You could format the cell as Text, or prefix the value with an apostrophe '. But if you perform calculations with the value, the result won't be precise.

@Hans Vogelaar 

Hello Hans,

I understand now i thought that it would be something about the amount of digits being to high.

I have now changed it as you have said and it has worked and the final digit is now 7.

Thank You very much for the help.

 

Best Regards

Darren