Using Excel to extract the date of birth from the identification number

In the conduct of staff information statistics, often used in the name, gender, ID number and date of birth, and other different fields. This information can be a single input workload will be very large, if entered the ID number, we do not need to date of birth, the date of birth can be extracted from the ID card number, the following is the specific implementation methods.

Since the ID card number may have 15 digits and 18 digits, the first need to make the identification number according to 15-bit and 18-digit distinction. The Excel software does not provide commands that are sorted by word count, and we can use the Len () function to achieve this. If the employee's ID number is recorded in column A, in the other column, for example, enter the "=len (A1)" function in cell D1, then drag the fill handle to the last cell, you can count the number of digits of each ID number, and then according to the D column, you can separate the ID number according to the number of digits.

After sorting, you first need to remove the number in front of the date of birth, the following 18-digit ID number as an example to introduce. Select the cell that contains all 18-bit ID numbers, and then execute the Data/column command, pop-up the 1th dialog box of the Text Breakdown Wizard, and click the fixed Width radio button in the original data type.

Click the Next button to enter step 2nd to set the field width (column interval) dialog box, click the mouse after the 6th number in the Data Preview dialog box, and a vertical line with arrows appears, dividing the ID card into two parts.

Click the Next button to enter the third step to set the data Type dialog box for each column, click the data on the left of the column to make it selected, select the text type in the list of columns data formats, click to select the data to the right of the line and set to the text type, and then click the OK button, and the ID number in column A is split into two columns. The top 6 bits are stored in column A, and the other data is stored in column B.

Then select the 12 digits in the split B column and execute the data/columns command, note that in step 2nd you set the column number after the 8th digit, and in step 3rd, set the left digit to date format, which extracts the 7th to 14th digits of the 18-bit ID number and stores it in the date format in column B. The 15-bit ID number is extracted in 18-bit, except that in step 2nd you need to set the break line to the 6th digit.