How to write more than 16 digit number in MS Excel
In Microsoft Excel, the default behavior is to display numbers with up to 15 digits accurately. If you need to display more than 16 digits in a cell, Excel may round off the last few digits automatically, leading to potential loss of precision. However, you can use a workaround to display more than 16 digits as text without Excel rounding off the number.
Follow these steps to write more than 16 digits as text in Microsoft Excel:
1. Enter the long number that you want to display in a cell.
2. Select the cell with the long number.
3. Right-click on the selected cell and choose "Format Cells" from the context menu. Alternatively, you can press `Ctrl + 1` (Windows) or `Cmd + 1` (Mac) to open the Format Cells dialog box.
4. In the Format Cells dialog box, go to the "Number" tab (if not already selected).
5. From the list of categories on the left, select "Text."
6. Click "OK" to apply the "Text" formatting to the cell.
By formatting the cell as Text, Excel treats the number as text, and you can now display more than 16 digits without any rounding. However, please note that this means the number will be treated as text, and you won't be able to perform mathematical calculations directly on it.
If you need to use the long number for calculations, you can store the original number in a different cell with the "General" or "Number" format and use the text-formatted cell for display purposes only. This way, you can preserve the precision for calculations while showing the full number as text.
Excel possesses an unexpected constraint that may come to light when attempting to input more than 15 digits in a cell. The application has a limited capacity to store numbers with a precision of 15 digits. Consequently, should a number with 16 digits or more be entered, Excel will automatically truncate those additional digits to zero. This limitation can potentially present challenges, especially when dealing with substantial values, such as credit card numbers.
To address this issue, if your spreadsheet necessitates displaying values surpassing 15 digits, alternative formatting methods will need to be employed.
Sometime this type of numbers are called Long Numbers
When the Long numbers are displayed incorrectly in Excel sheet
After
you are entering a long number such as a credit card number in the Excel cell,
the enter number is not displayed correctly in Excel cell. For example,
 |
| An Example for the Long number that is not shown correctly in Excel |
Default number format
in Excel is General therefore you can display up to 11 digits in a cell.
To
solve this issue, you may use one of the following methods.
Method 1: Format the cell as text format
To
do this, follow these following steps:
1. Right-click target cell, and then choose and click Format
Cells.
 |
| Format a cell as text by selection the format cell |
2. On the Number tab,
select Text, and then click OK.
3. Then type a long number. (Be sure to set the
cell format before you type the number)
 |
| type Long Number |
4. If you do not want to see the warning arrows,
click the small arrow, and then click Ignore Error.
 |
| Select Ignore Error if you don't want to see the warning arrows |
|
| The result of method 1. |
Method 2: Use a single quotation mark
When
you enter a long number, type a single quotation mark (') first in the
cell, and then type the long number.
For
example, type '1234567890123456789 and the quotation mark will
not be displayed after you press ENTER.
 |
| Add a single-quotation mark before typing a long number. |
As the default behavior in Excel, the maximum number of digits allowed in a cell is limited to 11. Should a 12-digit number be entered, Excel automatically converts it into exponent and power notation, representing the number in terms of the power of 10 by converting it into a decimal form. Unfortunately, this representation lacks accurate precision, making it unsuitable for dealing with large numbers in real-life scenarios, such as credit card numbers, which consist of 16 digits.
To address this issue and retain all the digits visible without the decimal representation, an alternative technique is available. Follow these steps:
1. Right-click on the cell where the large number needs to be added.
2. A menu will appear; select the "Format Cells" option.
3. The "Format Cells" dialog box will open. In this dialog, navigate to the "Number" tab.
4. Under the "Category" options, select the "Text" option. This will format the cell as text, preventing the automatic conversion of the credit card number into decimal form. Each digit will now be treated as a character, and Excel can accommodate up to 32,767 characters in a single cell.
5. Click "OK" to apply the formatting.
6. Re-enter the credit card number, and this time you will observe that all the digits are visible with 100% precision in the 16-digit number.
By utilizing this method, Excel allows the storage of numbers containing more than 11 digits while maintaining accuracy and visibility of each digit. It is anticipated that in forthcoming updates of Excel, Microsoft may increase this limit to 16 digits, enabling the convenient storage of credit card numbers without the need for formatting workarounds.
Important information of MS Excel
Tags
Microsoft Excel