Office hacks: more efficient operation to enhance the Excel batch replacement function

In Excel, the conventional method of data replacement is in the “Find” and “Replace” in order to enter the contents of the replacement. Obviously, if the need to replace more different keywords, the operation is not efficient. At this point, we can use other methods or tools to enhance the Excel batch replacement function.

● Use a good fuzzy search to specify conditions for batch replacement

If you need to replace a set of data, you can use the “*” sign for fuzzy search, and then batch replacement. For example, in the following table, if you want to replace all the data with “need to make up” for a grade less than 60, open “Find and Replace” and enter in the “Find Content” box “*”, click “Find All”, and then click “Value” in the pane below, so that the table will be automatically sorted by the scores of all the students in the subject, select the data with scores less than 60 as prompted. Then enter “need to make up” in “Replace with”, and finally click “Replace All” (Figure 1).


Using a similar method, we can easily batch select the eligible discontinuous data, for example, now need to be less than 60 points of data are filled with yellow, after selecting all eligible data, click “Start → Fill Color → Yellow” can be. In this way, you can later use the “Find → Format → format selected from the cell” to quickly find the fill color is yellow cells, you can also replace the data in these cells in bulk operations, such as setting the font color to red, or add a border and other operations (Figure 2).


● Multiple data can be easily replaced

The above method is suitable for replacing multiple different data into one type of name in bulk. It is not applicable if there are multiple data that need to be replaced with corresponding categories. For example, a company due to changes in technical standards, the need for the original document in the old standards, such as GX-ZZ-201801 changed to FJGX-201801CBZZ, that is, the original standard GX replaced by FJGX, ZZ-201801 replaced by 201801CBZZ, because each standard characters are different, using Excel to find the replacement is extremely inefficient. For similar formats of character replacement, you can use intelligent fill to quickly achieve. First in B1 in accordance with the replacement format of A1, enter FJGX-201801CBZZ, then press Ctrl + E, so that Excel will be based on A1, B1 format to determine the contents of the comparison intelligently, and down to fill, and finally the A column to delete. This saves the trouble of finding and replacing each character (Figure 3).


If the format of multiple data to be replaced is not the same, for example, “FJ-GX01-dzj01” should be replaced with “FJ-GX01/zj01” (i.e., the second “-” in the code should be changed to “/”) (Figure 4). ” to “/”) (Figure 4), because the second “-” is not in the same position in the original data, and the code format is different, the use of intelligent fill can not complete the fast replacement (of course, the built-in replacement component also (of course, the built-in replacement component can not choose to specify “-” for replacement), for similar replacement can be achieved with the help of Replace function. In C1, enter the formula “=REPLACE(A1,FIND(“-“,A1,6),1,”/”)”, then fill down to complete the replacement operation (Figure 5).



Formula explanation.

First use the Find function to find the location of the second “-“, here we should pay attention to the location of the second “-” in the original data characteristics. For example, the smallest position of the first “-” in the data is in the third character (i.e., FJ-), the largest position is the fifth character (i.e., AGER-), so you need to use FIND (“-“,A1,6), which means that from the sixth character of A1 to search for the location of “- ” position, so as to find the correct position of the second “-“, while returning the integer value as the Replace start replacement position. For example, “= FIND(“-“,A1,6)” returns “8”, and REPLACE(A1,8,1,”/”), which means the 8th character of A1 (i.e. the second “-“) is replaced, and 1 is replaced. “), replacing 1 character, the replacement symbol is “/”.

● Easy and Efficient Quick Replacement with VBA Script

The above method is suitable for the replacement of a single document, if you need to replace a lot of documents, such as the original workbook in a lot of Chinese names, such as “a division”, “two divisions” and so on, due to the entry management system can not identify the Chinese, now need to be all Chinese use the corresponding pinyin replacement. At this time, the use of the above function replacement is not convenient, you can use VBA script for quick replacement.

First sort out all the characters that need to be replaced, right-click on the name of the current worksheet and select “View Code” to open the VB edit window, select Worksheet in the first drop-down box, select BeforeDoubleClick in the second drop-down box, enter the code shown in the figure ( Please replace the characters by yourself when you actually use it, one line for each character you need to replace, code download:, extraction code: kubg), click “Run” to complete all replacements (Figure 6).


Since VB supports regular expressions, we can use it flexibly to make various substitutions. For example, in order to facilitate timely bookkeeping, the company’s cafeteria purchasing staff often use cell phone sticky notes, using a format such as “2020.06.03 purchase of winter melon 123 yuan” to bookkeeping, these records imported into the computer, you need to use the time and amount format to replace (Figure 7). To do this, first go to (extraction code: pxyf) to download the required code, open the VB editor window, enter the downloaded code, and click “Run”. You can also save it as a macro workbook, so that when you need to replace similar data in the future, just click “Development Tools → Macro → Data Conversion” (Figure 8).



Leave a Comment