Shulou(Shulou.com)11/24 Report--

As we all know, when sorting out the employee files, the staff of the personnel department need to extract the employee's date of birth through the ID card number.

Manual input is troublesome and error-prone, so is there any way to extract it quickly?

Little E, there are three ways!

01. Formula method enter the following formula in the E4 cell, and then double-click the fill to complete:

=-- TEXT (MID (D4dy7, 8), "0-00-00")

The specific meaning of the formula is as follows:

The MID function, which can intercept part of the content from a character.

In this case, MID (D4, 7, 8) says:

We want to extract eight characters from the seventh character of the D4 cell, and the result is "19880625", that is, the year, month and day of birth.

However, this result is not regular date data and needs further processing.

The TEXT function can modify the format of numerical values or display them through format codes.

In this case, we use the TEXT function to convert the previously obtained "19880625" to the "year-month-day" format, using the format code "0-00-00".

The result of processing is a string of words like "1988-06-25".

However, the result is not a real date, but a text string that looks like a date, so it needs to be processed again.

A mathematical operation that converts a character date into a real date.

In this case, we add two minus signs in front of the TEXT function to indicate that the negative operation has been performed.

If the negative is positive, the numerical size of the Operand itself does not change, but after such mathematical processing, the original character date can be transformed into the real date.

02. Sorting method Select the D4:D12 cell, and click [separate] under the data tab.

Select fixed width in step 1 of the text sorting Wizard, and click next.

In step 2 of the text sorting Wizard, the line is established after the mouse clicks the sixth character.

Also before the penultimate character, establish a separation line so that the 8-digit number of the date of birth is separated from the other characters on both sides, and continue to click next.

In step 3 of the text sorting Wizard, click column 1 and select do not import this column. Similarly, column 3 also select do not import this column.

Click column 2, the part of the code that represents the date of birth, and select the column data format as date.

This operation is the key to the whole process, which automatically converts 8-bit coding into standard date data.

Finally, select the E4 cell in the target area and click "finish" to get the result.

03. Basic usage ❶ Select the E4:E12 cell and customize the cell format as "yyyy-mm-d".

❷ enters "1988-06-25" in E4 cell and "1994-05-16" in E5 cell as examples.

❸ and then press [Ctrl+E] to quickly fill in to get all the dates.

To sum up: the ❶ function method is relatively flexible. When column D is filled with new data, the formula can be automatically updated by filling the formula directly down.

The operation of ❷ sorting method is relatively simple, but there are many steps.

❸ Quick padding is the simplest and most crude operation, but Quick padding is a feature that has been available since the Excel 2013 version and is not available in the lower version.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Li Daxiang Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.