Excel form input 18 ID card number is wrong, behind are 0, how to solve?

Scheme one: Change the cell format to "text" and re-enter it!

Option 2:1, address the problem of input ID number

By default, if the number of digits entered in the current cell in Excel is more than 11 digits (excluding 11-bit), the system displays the numbers entered in the scientific notation format, and when the number of digits entered exceeds 15 digits (excluding 15-bit), the system displays all 15-digit numbers as "0". This way, if we want to enter the ID number (15 or 18 digits), the ID card number will not be displayed correctly. At this point, there are two ways to solve this problem:

1, the use of digital labels

Select the cell (range) where you want to enter your ID number, open the Format Cells dialog box, enter the Number tab, select the Text option under category, and then enter your ID number. [Be sure to format, and then enter the number]

2, in the input ID number, in front of the number plus an English state of the single quotation mark "", you can let the ID card number fully displayed (the single quotation mark is not shown after the confirmation).

Second, let the cell automatically add ID card in front of the same part

You know, the first 6 ID card number, is the province, city, district (county) code (such as Zizhong County is "511025", etc.), if the holder is Zizhong County, enter such ID number, you can make the cell format "number" Labels help you reduce the amount of work you enter: Select the range of cells that you want to enter your ID number. To open the Cells dialog box (or right-click the Format cell), click the format for the menu bar above Excel to enter the default Number tab, select the following Custom option under category, and then on the right Type in the box below: "511025" @, OK exit. After entering the ID number 7th and after the number (such as "220324621"), the system will automatically precede the corresponding code to form a complete ID number (such as "511025220324621"). If this is a 18-bit ID number, this is a good setting: Enter the "51102519" in the box under "type" on the right side of the custom option, and make sure to exit. After entering the ID number 9 digits and subsequent numbers (such as "2203246211"), the system will automatically precede the corresponding 51102519, forming a complete ID number (such as "511025192203246211").

Similarly, other inputs that need to be entered with the same part of the number can be entered in this way.