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

Original title: "when Ctrl+E encounters search and replace, this new feature is easy to cry!" "

The author: Zhu Lan

Editor of this article: Zhu Lan

Today, I received a request for help from a friend whose company is going to give a small gift to employees who celebrate their birthday.

You need to extract the birthdays of each employee from the following table.

▲ simulated data before she encountered similar problems is directly copy and paste, but this time there is too much data, I wonder if there is any faster way.

In fact, there are many ways, for example, we have shared before:

❶ shortcut method: [Ctrl+E]

❷ function formula method:

= MID (B2jing7jin8) but birthdays extracted by these two methods are all connected year, month and day:

It's troublesome to distinguish at a glance.

So why don't we just divide the data into segments? Like this:

Or this:

This paper focuses on two methods, which are advanced versions of the above two methods.

First of all, let's look at the first method.

1. Ctrl+E & replace the steps to find 👉:

❶ in the first cell, enter the birthday in the ID card in the format of "XXXX-XX-XX S" (where the S can be changed into any letter), then select the following cell and press [Ctrl+E] to quickly fill it.

The effect picture is as follows:

❷ press the [Ctrl+H] key, bring up the find and replace window, replace "S" (space + S) with empty, and click replace all.

PS. [Ctrl+E] only version 2013 or above is available.

Wait, wait,

Of course I thought about it, and I tried it, and guess what?

Effect picture:

Obviously, the result was a mess.

[Ctrl+E] is really powerful, but it's not everything after all.

Well, since this method doesn't work, let's honestly follow the steps I said before.

But!

If some students' computer version is less than Office 2013, they cannot use the [Ctrl+E] shortcut key.

In this case, how to solve the problem mentioned above?

The method is also very simple, using the function formula is enough!

2. the function formula method comes straight to the point. let's first look at the formula directly:

= TEXT (cell of MID ID card number 7Pol 8), "0000-00-00")

The formula in the figure is also simple and easy to understand.

Two functions, TEXT and MID, are used.

Among them

❶ MID (the cell where the ID card number is located, 7pc8)

The role of the MID function is to extract the number representing the birthday from the ID card number, that is, digits 7 to 14.

From left to right, the 7th digit is a total of 8 digits, so parameter 1 is 7 and parameter 2 is 8.

❷ TEXT (numeric, "0000-00-00")

The function of the TEXT function is to convert the values extracted by MID into the 0000-00-00 format.

3. How about summing up? Are these two methods very simple?

Excel rookies can also be easily learned and used.

The first method, which skillfully combines [Ctrl+E] with the find and replace function, makes the smart fill work properly by occupying a place, and then replaces the placeholder, you can get the correct result.

In this way, we have shared similar tutorials before-long form printing:

▲ @ Changxiaan also uses substitution function and space occupation to convert one column of data into multiple columns, and the two are similar.

By the way, links to articles for long form printing tutorials are here at ↓↓↓

How can I print the form if it is too long? A small function, easy to do!

Students who are interested can also take a look at it.

After reading the article, I believe many students would like to say:

Why not introduce the cell format?

Press [Ctrl+1] to call up the [format Cell] window, custom format to 0000-00-00, is also very simple?

Of course, this is a method, but the data made in this way is only a "virtual table" and appears to be a date, but it is still a number in the actual cell and cannot be used for date calculation.

Each of the three methods mentioned above has its own advantages and disadvantages. Please choose according to the situation.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: 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.