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

How to Input Correct ID Number in Excel 2010-2016

If you enter a long number (such as a credit card number) in an Excel cell, the last digits are changed to zeroes, which is often not what you want. That’s because Excel has a maximum of 15 digits of precision and will round any numbers that follow the 15th digit down to zero.


To solve this problem, you can use these ways:

Way 1: Use a Single Quotation Mark in the Cell

When you enter a long number, type a single quotation mark ( ) first in the cell, and then type the long number. For example, type’ 6007003940080071977 in the cell like picture shows.

The quotation mark will not be displayed after you press Enter.

Use a single quotation mark

Way 2: Format the cell as text, then type the number.

If you are using credit card numbers, or other number codes that contain 16 digits or more, you must use a text format instead of a number format. If you type the number in the cell that is formatted as text, all the characters remain as you type them. Use these steps below to format the cell as text.

Step 1: Right-click target cell, and then choose 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’ll disappear the warning arrows, click the small arrow, select Ignore Error.

display-a-normal-number