Forum Discussion
Custom Formatting in Cells
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.
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:
- Select the cell with the 16-digit number.
- Go to the "Number Format" dropdown in the Excel Ribbon and choose "Number" or "General."
- If Excel is displaying your 16-digit number in scientific notation (e.g., 8.12345E+15), change the format to "Number" to display the full 16-digit value.
- If your number still isn't displaying as expected, it could be due to a limitation in Excel, which may not display numbers longer than 15 digits accurately. Excel stores numbers as double-precision floating-point values, which can lead to precision loss for very long numbers.
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:
- Preformat the cell as text by selecting the cell or cells and changing the cell format to "Text" before entering the 16-digit number.
- Enter your 16-digit number, and Excel will treat it as text rather than a number. This should prevent any scientific notation or rounding issues.
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:
- Text Format: The most reliable way to display a 16-digit number accurately is to store it as text. You can do this by formatting the cell as text before entering the number. To format a cell as text, follow these steps:
- Select the cell(s) where you want to display the 16-digit number.
- Right-click and choose "Format Cells."
- In the "Number" tab, select "Text" as the category.
Once the cell is formatted as text, enter your 16-digit number, and it will be displayed exactly as entered.
- Apostrophe Prefix: You can also enter the 16-digit number with an apostrophe (') as a prefix. Excel will treat it as text, and the apostrophe won't be displayed. For example, entering '1234567890123456 will display as 1234567890123456.
- Use a Helper Cell: If you need to perform calculations with the 16-digit number, you can store the exact number as text in one cell and use a separate cell for calculations. You can refer to the text cell and convert it to a number when needed for calculations.
- Custom Formatting: You can try custom number formatting to display 16-digit numbers. While custom formatting is limited to 15 digits in some versions of Excel, you can try using the following custom format to display all 16 digits:
- Right-click the cell and choose "Format Cells."
- In the "Number" tab, select "Custom."
- In the "Type" box, enter the following custom format: 0000000000000000
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.
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:
- Select the cell with the 16-digit number.
- Go to the "Number Format" dropdown in the Excel Ribbon and choose "Number" or "General."
- If Excel is displaying your 16-digit number in scientific notation (e.g., 8.12345E+15), change the format to "Number" to display the full 16-digit value.
- If your number still isn't displaying as expected, it could be due to a limitation in Excel, which may not display numbers longer than 15 digits accurately. Excel stores numbers as double-precision floating-point values, which can lead to precision loss for very long numbers.
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:
- Preformat the cell as text by selecting the cell or cells and changing the cell format to "Text" before entering the 16-digit number.
- Enter your 16-digit number, and Excel will treat it as text rather than a number. This should prevent any scientific notation or rounding issues.
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:
- Text Format: The most reliable way to display a 16-digit number accurately is to store it as text. You can do this by formatting the cell as text before entering the number. To format a cell as text, follow these steps:
- Select the cell(s) where you want to display the 16-digit number.
- Right-click and choose "Format Cells."
- In the "Number" tab, select "Text" as the category.
Once the cell is formatted as text, enter your 16-digit number, and it will be displayed exactly as entered.
- Apostrophe Prefix: You can also enter the 16-digit number with an apostrophe (') as a prefix. Excel will treat it as text, and the apostrophe won't be displayed. For example, entering '1234567890123456 will display as 1234567890123456.
- Use a Helper Cell: If you need to perform calculations with the 16-digit number, you can store the exact number as text in one cell and use a separate cell for calculations. You can refer to the text cell and convert it to a number when needed for calculations.
- Custom Formatting: You can try custom number formatting to display 16-digit numbers. While custom formatting is limited to 15 digits in some versions of Excel, you can try using the following custom format to display all 16 digits:
- Right-click the cell and choose "Format Cells."
- In the "Number" tab, select "Custom."
- In the "Type" box, enter the following custom format: 0000000000000000
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.
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!
