What is the formula for converting old and new ID numbers

How to convert ID number to digital

1, open excel table, enter ID number in the cell.

2, in cell B1, enter the “=” symbol.

3, then enter the complete formula: = “G” &A1, G double quotes need to be entered in English mode.

4, click enter, you can generate the results of the calculation, the following figure has been “G” added in front of the ID number.

5, drag the lower right corner of cell B1, you can copy the formula down, add bulk operations.

excel how to turn the old ID number into a new

15-bit ID card up to 18:

A1 for 15-bit ID number:


=IF(LEN(A1)=15,REPLACE(A1,7,,19)&MID(“10X98765432”, MOD(SUM(MID(REPLACE(A1,7,,19),ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1),A1)

Array formulas, after inputting the formula, press ctrl+shift+return End

How to convert 15-bit ID number to 18-bit?

First of all, recognize the 15-digit ID number:

15-digit ID number: 511702800222130.

The first six digits indicate the address code, which is accurate to the county.

The six to 12 digits indicate the date of birth in yyyMMdd format.

The last three digits, the sequential code, indicate the sequential number within the same area, with odd numbers of the sequential code assigned to males and even numbers to females.

First, the 15-digit ID number’s date-of-birth digits are completed in yyyy-MM-dd format.

For example, the original 15-digit date-of-birth bit 8010222 is changed to 19800222, and the modified number becomes. 51170219800222130.

The above modified 17-digit number to do the calculations:

Multiply each of the above numbers in the order of the set of digits of the weighting factor below and sum them separately. .

Weighting factor {7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}.

For example

5*7+1*9+1*10+。。。。 +0*2=224.

Calculate the modulus of the sum obtained above, and then according to the value of the modulus get the data of the corresponding position in the check digit array (note: the position index is counted from 0) as the last bit of the 18-bit ID card.

The check digit array {“1”, “0”, “X”, “9”, “8”, “7”, “6”, “5”, “4”, “3”, “2”}.

If the above sum is taken as the mode of 11, as follows:


The result of the calculation is 4, then take the value of the 5th bit of the check digit array 8 as the last bit of the 18-bit ID card,

Then we have calculated the transformed 18-bit ID card number as follows:

This is the first time that the ID card number is taken as the last bit of the 18-bit ID card,

This is the first time that the ID card number is taken as the last bit of the 18-bit ID card.

Resident identity card, divided into real card identity card and EIDCard (electronic identity card), is used to prove the identity of the holder of a legal document, mostly issued by the national or regional government to the citizens. It serves as an important identification document for each individual.

The People’s Republic of China (PRC) Resident Identity Card (RIC) is referred to as “ID card”, and on April 6, 1984, the State Council issued the “Regulations of the People’s Republic of China on the Trial Implementation of Resident Identity Cards”, and started to issue the first generation of RICs.

Since March 29, 2004, mainland China has officially begun to issue residents with second-generation resident ID cards with built-in non-contact IC card smart chips. The second-generation ID cards use anti-counterfeiting film and printing anti-counterfeiting technology on the surface of the ID card, use personal color photographs, and can be machine-readable information in the digital chip.

On December 25, 2017, the “WeChat ID card online application credentials” were issued in Nansha District, Guangzhou City, providing national legal document-level identity authentication services for online and offline government services as well as real-name application scenarios, such as hotel industry registration and logistics and shipping.

The national emblem side is the front side and the portrait side is the back side.

The ID card is not unique to today, but has been around for a long time. However, it originated as an identifier for officials.

The earliest ID card is the period of the Warring States period, Shang Yang in the State of Qin to change the law, the invention of the photo body post. Issued by the government, it was a smooth and finely polished bamboo board with the holder’s headshot and place of origin inscribed on it. People in the country were required to have it, and if they didn’t, they were considered black, or spies, or something like that.

Reference: ID card_Baidu Encyclopedia