Excel function application--ID card Birthday Extract

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.