Automatically extract birthdays and genders from your ID number in Excel

Each year, the new first-year students need to report to a higher education department a spreadsheet containing the ID number, birth date and so on, in preparation for the establishment of a unified provincial electronic status files. Hundreds of freshmen, you have to enter hundreds of rows of corresponding data, this is not a cushy job. Is there any way to reduce the amount of input and improve efficiency? In fact, we only need to complete the Student ID card in Excel, it can help us to fill out the date of birth and sex automatically.

Now the student's ID card number is already all 18 new generation ID card, the figure inside is 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 "extract" some of the digital ideas.

1, convert ID number format

We first complete the student ID number into the Excel 2003 table, when the default is the "number" format (the cell is displayed in the scientific notation format), 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.

2, "extract" the date of birth

Place the cursor pointer over the cell in the date of birth column, taking the C2 cell as an example. Then enter "=mid (b2,7,4) &" Year "&mid (b2,11,2) &" Month "&mid (b2,13,2) &" Day "(note: external double quotes do not need to be entered, Symbols such as quotes and commas in a function should be entered in the English state. When you enter, you will notice that the student's birth date has already appeared in the C2 cell. Then, after selecting the cell, drag the fill handle, and the corresponding date of birth appears in the other cells. As shown in Figure 1.

Fig. 1 by means of the above method, the system automatically obtains the information of the birth date

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

3. Judge gender "men and women"

Select the cell for the sex column, such as D2. Enter "=if (mid b2,17,1)/2=trunc (Mid (b2,17,1)/2)," female "," male "(note as above), return, the student" is a male or female "has been obediently judged out. Drag the fill handle so that the other students ' gender is also automatically entered. As shown in Figure 2.

Figure 2 Sex is automatically filled in the specified position

In this way, with two simple functions, we can get Excel to automatically extract the birth date and sex from the ID number and fill it into the cell, which greatly reduces our input workload. The final effect is shown in Figure 3.

Figure 3 Use EXECL to automatically identify and fill in student information