Excel function application--ID card Birthday Extract

One, only 18-digit ID number

Such as:

ID number

330682199302264000

41120019890823729X

231081199002256839

131101198203154666

360300198205270958

11010519491231002X

Calculation steps:

Step1: Extract Birthday string: =mid (a2,7,8)

, birthdays start from the seventh place, a total of eight.

Step2: This step can be divided into two ways: method one: Using the Split tool 1. Copy the date that the formula was generated:

2. Paste in the same column as "value" in the format:

3. Select the birthdays that need to be broken down:

4. Click "Columns" in "Data":

5. Follow the steps below:

Tick "fixed width"

To create a split line, click in the corresponding red area between the birthday year and month and the month and date:

Tick "date" in the third step and click Finish:

The string is decomposed into three separate units:

6. In the new column, enter the formula =date (B2,C2,D2), which was completed before the month and day.

You can get the ID card to extract the birthday.

Method Two: Use equation 1. Displays the string in 0-00-00 Format: =text (B2, "0-00-00")

One step to complete.

Second, only 15-digit ID card number

This kind of situation basically does not appear, even if appeared, the specialized column also did not need, directly enters the next section.

Three, 15-digit ID card number and 18-digit ID card number coexist

For example:

Step1: Extract Birthday string:

18-digit ID card, starting from 7th place, extract 8 numbers;

15-digit ID, starting with the 7th digit, extracts 6 digits.

It is therefore necessary to use the IF function to select the length of the extracted bits according to the length of the ID number:

=mid (A2,7,if (LEN (A2) =18,8,6))

Step2: The extracted string is represented in a specific format: =text (MID (A2,7,if (LEN (A2) =18,8,6), "0-00-00")

Step3: Convert to Time series

Now it is necessary to solve the problem that the 15-bit ID card date shows the error:

The workaround is to turn it into a time series by multiplication:

=1*text (MID (A2,7,if (LEN (A2) =18,8,6), "0-00-00")

Step3: Last converted to date =text (1*text (MID (A2,7,if (LEN (A2) =18,8,6), "0-00-00"), "YYYY-MM-DD")

The previous text function cannot be converted to "YYYY-MM-DD" because the first two bits of the year are missing and can now be converted to a date directly from the time series.

Note: YYYY-MM-DD is one of the date formats, and 0-00-00 is only a display format for text.

Iv. Final Plan B

After calculation, the IF function determines the number of characters selected, which is consistent with the result of Len (A2)/2.2.

=text (1*text (MID (A2,7,len (A2)/2.2), "0-00-00"), "YYYY-MM-DD")

V. Age of calculation

As an extension, it is possible to calculate his/her age by date.

=year (Today ())-year (C2)

The function today () does not require parameters to return the date immediately, as in 2008-08-08.

The year of the function returns the date.

Excel function application--ID card Birthday Extract