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