Extracting 18-digit ID number information with Excel culvert number

The 18-bit new ID card, the numbers inside are regular. The first 6 digits are the code of domicile, 7-14 is the date of birth. The 17th "2" stands for gender, even for females, and odd for males. All we have to do is to use some of these numbers to "extract" the number.

We started Excel2003 and entered the ID number that needed to extract the information.

The ID number you enter will be displayed in scientific notation and you need to change the number format. When you have selected all the ID numbers in the column, right-click and select Format Cells. In the pop-up dialog box, in the Number tab, set the category to text, and then click OK.

First, to extract the date of birth

Place the cursor pointer over the cell in the date of birth column, taking the C5 cell as an example. Then enter "=mid (b5,7,4) &" Year "&mid (b5,11,2) &" Month "&mid (b5,13,2) &" Day "

Note: The outer double quotes do not need to be entered, and symbols such as quotes and commas in the function should be entered in the English state.

When you enter the car, you will notice that the date of birth has already occurred in the cell C5. Then, after selecting the cell, drag the fill handle, and the corresponding date of birth appears in the other cells.

The mid function is an Excel-provided function command to extract part of a character from a string, which prompts you when you enter mid in Excel.

Bring it down, we'll get the sex information.

Select the cell for the sex column, such as D5. Enter "=if" (Mid b5,17,1)/2=trunc (Mid (b5,17,1)/2), "female", "male", and then enter, automatically determine the gender. Drag the fill handle so that other genders are also automatically entered.

Finally, we're going to calculate the age

Where C5 is the date of birth, double-click the column of age, and enter the following formula =year (now ())-year (C5)

After the carriage return, you can get the age, copied to other cells can be completed. In this way, no matter from the body from the evidence to extract any information can be achieved by this method.