iSumsoft » Resources » Office » How to Input Correct ID Number in Excel 2010-2019

How to Input Correct ID Number in Excel 2010-2019

When entering a long serial number ( such as credit card numbers ) in an Excel cell, the last digit will change to zero, which is not what you want. The reason why the last digit of the credit card number is changed to zero is that the Excel has a maximum of 15 digits of precision and will round any number following the 15th digit to zero. To work around this issue, you can use the following methods:


Method 1: Use a single quote first in the Cell

When entering a long number, first type a single quotation mark (') in the cell and type the long number. For example, type "6007003940080071977" into the cell.

And the quote will disappear when you press Enter.

Use a single quotation mark

Method 2: Use a text format instead of number format

If you are using a credit card number or other numeric code that contains 16 or more digits, you have to change to use a text format instead of a number format. If you type a number in the cell formatted as text, all characters are preserved as you type. Here's how:

Step 1: Right-click on the target cell and select Format Cells.

Set format cell

Step 2: On the Number tab, select Text, and then click OK.

Format cells as text

Step 3: Then type a long number after setting the cell format.

Type long number in excel sheet

Step 4: It will disappear the warning arrow, click the small arrow and select Ignore Error.

Display a normal number

That's it!