Nov 01 2023 10:40 AM
Nov 01 2023 10:40 AM
I am trying to translate a 16-digit number to the EXACT 16-digit number in the cell? I've done this in Custom Formatting (typing just 16 -0s) and certain numbers just don't work (ones that starts with 8). How do I correct this? I've been figuring out work-arounds for 6 years. Please advise.
Nov 01 2023 11:22 AMSolution
Custom formatting in Excel can be useful for displaying numbers in a specific way, but it doesn't change the actual value in the cell. If you're experiencing issues with certain 16-digit numbers, particularly those starting with 8, not displaying as expected, it might be due to Excel treating them as scientific notation.
Here's how to correct this issue:
If you need to work with and display 16-digit numbers without any rounding or scientific notation, you might want to consider storing these numbers as text.
To do this:
Keep in mind that if you need to perform calculations with these numbers, you may need to convert them to numbers temporarily for calculations and then format them as text again for display.
Remember that custom formatting (even with many "0"s) won't change the underlying value of the cell; it only changes how the value is displayed. For exact preservation of 16-digit numbers, storing them as text is a reliable approach.
Here some steps you can take certain to display 16-digit numbers as accurately as possible:
Once the cell is formatted as text, enter your 16-digit number, and it will be displayed exactly as entered.
Please be aware that Excel's limitations in precision may lead to rounding or display issues when working with very long numbers. Storing the number as text is the most reliable way to ensure that Excel displays and stores the exact 16-digit number as entered.
Nov 01 2023 11:48 AM
@NikolinoDE Thank you so much! I think you were correct about the number being over 15-digits, so I was able to enter it as a text and it worked perfectly. I can't believe after 6 years, I finally figured this out (thanks to you!). Thank you, thank you, thank you!