In the previous article "Practical Excel skills sharing: cleverly using functions to create an automatic statistical inventory table", we learned four functions and used them to make an automatic statistical inventory The purchase, sale, inventory and warehouse statistics table. Today we will share some practical operations of "find and replace". It turns out that you can use it to filter date data. Let's take a look!
When setting the date to filter, how can you filter by year and month? Let’s answer this question today.
As shown in the figure below, column F is the employee’s entry date, but the date at this time is not in the standard date format. The default standard date format in excel is "-" "/" or the Chinese "year and month" "day" to separate the year, month and day numbers. At this time, I want to filter the table to select employees who joined the company in February 2018.
First add a filter button to the table (Start-Data-Filter). In the drop-down menu of entry date, the data is not divided by year and month. At this point we need to convert the date into a standard date format.
Press ctrl H to replace the shortcut key. In the pop-up dialog box, enter a period in English format for "Find content" and enter "/" in English format for "Replace with" or"-". After setting, click Replace All.
You can now see the replacement results, and all dates have become standard date formats.
Click the drop-down button of the entry date, and you can see that the date is divided into years and months. Click the " " and "-" in front of the number to expand and collapse the data.
We check 2018 and February in the drop-down menu to see the filtering results.
Expand knowledge: Two practical techniques for "find and replace"
1. Batch deletion of invisible characters
When there are a large number of the same characters in our table that need to be deleted, or when we have several worksheets in a workbook that all need to delete the same data, if we have one Deleting it alone is a waste of time. At this time, you need to use the find and replace tool.
As shown in the figure below, sum the F, G, and H columns in Table 1, Table 2, and Table 3. The total salary payable in column H displays 0. When the function operation result is 0, it means that the cell content referenced in the function is not a number. In addition, the number in column H is to the left (if the left-aligned format is removed, it is still to the left), which means it is text type. number.
(Friends who don’t know the shortcut for summation can read the previously published tutorial and click on the link: //m.sbmmt.com/topic/excel/491692.html)
Let’s first check what went wrong with the data.
Select cell H2 and drag the mouse to select the number in the edit bar. You can see that there are invisible characters in front and behind the number (if there are no characters, dragging the mouse will not select it. If it is selected, it means There are non-whitespace invisible characters). This is what often happens when people export data from the system or copy text from the Internet. Even if the format is cleared, the sum still cannot be added.
Let’s talk about the solution.
In the edit bar, select the invisible characters and press "ctrl C" to copy.
Press the find and replace shortcut key ctrl H to pop up the replacement dialog box. Press "ctrl V" in "Find Content" to copy invisible characters. At this point you can see that the mouse cursor has moved a few spaces to the right.
Do not enter any characters after "Replace with" to indicate that the search content will be deleted. Click the "Scope" drop-down menu to select a workbook. At this time, you can find and replace all worksheets in the entire workbook. Click "Replace All".
At this time, you can see that the data in column H automatically displays the summation result.
Sharp-eyed friends may find that the data is still on the left. This is because left alignment is set in the alignment. We click "Left Alignment" again in the alignment. "Cancel the "left-aligned" format.
At this time, if you look at the tables in Table 2 and Table 3, you will find that all invisible characters have been deleted simultaneously, and the correct result of the total is automatically displayed. .
#2. Batch replacement cell format
In the table below, due to the good performance of Nobita and Shizuka that month, the boss said They wanted to increase their wages individually, so the finance department made a special cell format for their names. The next month, the table needed to be restored to a unified format. The following case mainly talks about batch replacement of color formats. In addition, various cell formats can be replaced in batches.
Press the replacement shortcut key ctrl H. In the pop-up dialog box, click the format drop-down button. At this time, you can see that there are two options available.
When we know the format of the cell, click the first option "Format" and select the corresponding color in the pop-up frame.
When we are not very clear about the format of the cell, click the second option "Select format from cell", and the mouse will turn into a straw. Tool that can directly absorb the color of cells. Here I will directly absorb the color of B7 or B11. You can now see a preview of the color.
The following "Replace with" settings are the same as before. Select the second item "Select format from cell" in the format drop-down menu and absorb any blue cell. Just click the grid. At this point you can see the results below.
If we have multiple worksheets that need to be replaced, just select the workbook in the "Scope" drop-down menu. After setting up, click "Replace All" and you will see that the table changes to a unified color format.
Note: The "Find and Replace" dialog box will remember the last format set, so you need to select "Format" when using Find and Replace again. The "Clear Lookup Formatting" option in the drop-down menu clears the formatting.
Related learning recommendations: excel tutorial
The above is the detailed content of Practical Excel skills sharing: Use 'Find and Replace' to filter date data. For more information, please follow other related articles on the PHP Chinese website!
