Office hacks: extracting useful information from ID numbers

Visiting many organizations often involves the registration of personal identification information. In the registration of personal information, in addition to the ID card number, usually also registered together with the gender, age, place of origin and other information (Figure 1). In fact, for the management should know that the ID card number itself contains this information, through a simple Excel function, you can easily convert these information, thereby simplifying the process.

1814A-SFXXTQ-1

Rights statement: ID numbers used in this article are randomly generated by the machine, if there is a resemblance is purely coincidental. This case does not involve the name corresponding to the ID card number.

1. Calculating gender

The 17th digit of the ID card number is the gender marker, where the even number refers to female, odd number refers to male. As in Figure 1, in cell B2, enter “= IF (MOD (MID (A2, 17, 1), 2),” “male”, “female”)” and fill down, you can get each number corresponds to the gender.

2. Calculate age

The most important feature of age is that it changes with time. In order to calculate the current age, we introduce functions such as TODAY and YEARFRAC. The principle is to calculate the date of birth from today’s time, the units of this time will be converted to years, and then rounded operation. The formula is not complicated, in cell D2 enter “= INT (YEARFRAC (DATE (MID (A2, 7, 4), MID (A2, 11, 2), MID (A2, 13, 2)), TODAY (), 3))” will be able to easily complete.

3. Calculate the place of origin

The first 6 digits of the ID number are the address code, we can get the place of origin by looking up the administrative division code specified in GB/T2260. Make an empty table of administrative division codes named “Address Table”, the data in the table can be obtained from the website of the Ministry of Civil Affairs of the People’s Republic of China or other government websites, copy the relevant data and paste it into the “Address Table” (Figure 2).

1814A-SFXXTQ-2

In the personal information table using the VLOOKUP function will be able to quickly output the address. In cell E2, enter the formula “=VLOOKUP(LEFT(A2,6), address table! $A$1:$B$3465,2,FALSE)” and fill it.

Tip: In the use of VLOOKUP function to match the digital symbols, it is best to convert all digital unity to text format. The method is as follows: Figure 2, select the data in column A, press Ctrl + C to copy, click the “Data” menu under the “Breakdown” button, in the pop-up dialog box, click twice in turn “Next “, then select “Text” and click “Finish” (Figure 3).

1814A-SFXXTQ-3

Extended reading: Calculating ID card verification code

The check digit is the last digit in an ID card and is used to check whether the ID number is legitimate. It is based on the first seventeen digits of the code, according to ISO 7064:1983.MOD 11-2 check code calculated. 4+MID(A2,7,1)*2+MID(A2,8,1)*1+MID(A2,9,1)*6+MID(A2,10,1)*3+MID(A2,11,1)*7+MID(A2,12,1)*9+MID(A2,13,1)*10+MID(A2,14,1)*5+MID(A2,15, 1)*8+MID(A2,16,1)*4+MID(A2,17,1)*2),11),{0,1;1,0;2, “X”;3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,FALSE))=CODE((RIGHT(A2,1))), “Correct”, “Error”) “. If the ID number meets the rules, then display “correct”, otherwise display “error”.

Leave a Comment