This article explains how to use wildcards or regular expressions to batch find and replace keywords across multiple Excel spreadsheet files, suitable for batch modification of numbers, codes, area codes, product codes, personnel IDs, and similar scenarios. Through HeSoft Doc Batch Tool , you can import multiple xlsx files at once, set the cell text processing range, and use rules like 030\d{3}, 46\d{3} to match similar content, then batch replace them with specified values, reducing the repetitive work of opening Excel files one by one for manual find and replace.
In daily office work, many Excel tables do not simply need a single fixed word changed, but rather require batch replacement of a set of similar content according to certain patterns. For example, hospital numbers, regional codes, product codes, order numbers, personnel IDs, etc., may have the same prefix and varying suffixes. Using Excel's built-in Find and Replace often requires opening files one by one and executing rules individually; with many files, this becomes very time-consuming and prone to omissions.
The problem addressed in this article is: how to use wildcards or regex-like rules to batch find and replace keywords in many Excel table files. The following uses HeSoft Doc Batch Tool as an example to demonstrate how to uniformly replace content matching specific rules in multiple xlsx files. In the example, the Hospital CCN column in the table contains numbers like 030006, 030010, 030011, and the CBSA column contains codes like 46060. We will use rule matching to replace six-digit numbers starting with 030 with 111111, and five-digit codes starting with 46 with 22222.
Applicable Scenarios
This Excel batch find and replace method is suitable for processing content with patterns, rather than replacing just one completely fixed keyword. Common scenarios include:
- Batch masking of ID numbers, account numbers, or document number fragments in Excel files.
- Uniformly replacing old codes with new codes across multiple xlsx and xls tables.
- Batch processing of rule-based fields in departmental reports, hospital lists, customer lists, and product lists.
- Situations where similar prefix numbers, such as those starting with 030 or 46, exist in multiple workbooks and need uniform replacement by rule.
- The need to process multiple Excel files simultaneously, avoiding manual file-by-file find and replace.
For those who frequently handle large numbers of tables, the core value of such office software lies in batch processing files, consolidating repetitive actions like clicking, copying, finding, and replacing into a single task, thereby reducing the rate of human error.
Effect Preview: Before and After Processing
Before Processing: Multiple cells contain numbers matching the rules
In the Excel table before processing, column B (Hospital CCN) contains multiple six-digit numbers starting with 030, such as 030006, 030010, 030011, 030064, etc.; column D (CBSA) also contains multiple five-digit numbers starting with 46, such as 46060. Red arrows in the screenshot mark the areas requiring batch replacement.
If done manually, one would need to search for specific values like 030006, 030010, 030011 in each workbook, or use multiple rule-based searches, which is highly inefficient. More troublesome is that these numbers are distributed across different files, worksheets, and even columns, making manual checks prone to omissions.
After Processing: Rule-matching content has been uniformly replaced
After processing is complete, numbers matching the 030\d{3} rule are replaced with 111111, and numbers matching the 46\d{3} rule are replaced with 22222. The image below shows that the multiple numbers starting with 030 in column B have become 111111, and codes like 46060 in column D have also become 22222.
This demonstrates that batch find and replace is not limited to a single fixed value, but can match a category of text based on rules. For tasks like batch modifying Excel keywords, batch replacing codes, and batch masking table data, this method is more flexible.
Operating Steps
Step 1: Enter the Excel tool and select Find and Replace Keywords in Excel
Open HeSoft Doc Batch Tool , and select Excel Tools from the function categories on the left. The interface shows several Excel-related functions, the first of which is Find and Replace Keywords in Excel. This function is used to batch find and replace keywords in Excel file contents, which perfectly matches the task to be completed in this article.
The purpose of selecting this function is to enter a wizard process specifically designed for Excel content replacement. Unlike the Find and Replace within a single Excel file, here you can import multiple files in batch, set unified processing rules, and execute them all at once.
Step 2: Add the Excel files to be processed
After entering the function page, the top of the interface displays the current task name as Find and Replace Keywords in Excel. The first step is to select the records to be processed; you can click Add Files, or use Import Files from a Folder. The screenshot shows 3 xlsx files already added: product_list.xlsx, team-participant-list.xlsx, and test.xlsx, with the bottom summary showing a record count of 3.
The purpose of this step is to consolidate all Excel files requiring batch keyword replacement into the same task list. After importing, it is recommended to check the names, paths, extensions, and other information to confirm no wrong files were selected. If a file does not need processing, it can be removed via the delete button in the operation column. After confirming everything is correct, click Next to proceed to the processing options settings.
Step 3: Set Excel processing scope and data type
On the Set Processing Options page, you first need to confirm the processing scope. In the screenshot, Cell Text is checked, indicating that this replacement targets the text content within Excel cells. The interface also shows options like the names of worksheet sheets and text on shapes within the worksheet, but since this example focuses on replacing table cell codes, only checking Cell Text is sufficient.
For the cell formula types to process, the screenshot shows No Limitation selected; for the processing method of cells containing formulas, Calculated Value of Formula is selected; and for the cell data type to process, Text is checked. For code-like data, especially content with leading zeros like 010039 or 030006, processing as text is safer to prevent the numeric format from causing the loss of leading zeros.
The interface also provides a setting for cell data type conversion. The screenshot indicates: If automatic conversion to the pre-processing data type fails, then convert this cell to Text. This setting is very useful for batch replacing code fields, as codes may look like numbers but their actual business meaning is usually text encoding.
Step 4: Choose the fuzzy search method and enter wildcard regex rules
Continue down the same page to the Keyword Options section. For the search method, the screenshot shows Use Formula to Fuzzy Find Text is selected. Then, in the List of Keywords to Find, two rules are entered: 030\d{3} and 46\d{3}. These patterns can be understood as matching text using wildcards or a regex approach, where \d represents a digit and {3} represents a sequence of 3 consecutive digits.
In this example, 030\d{3} is used to match content starting with 030 followed by 3 digits, such as 030006, 030010, 030111, etc.; 46\d{3} is used to match five-digit codes starting with 46 followed by 3 digits, such as 46060. In the List of Keywords After Replacement on the right, row 1 is filled with 111111, and row 2 is filled with 22222, meaning the content matched by the first rule on the left is replaced with the first result on the right, and the content matched by the second rule on the left is replaced with the second result on the right.
It is critically important here to pay attention to the row number correspondence between the left and right sides. During batch replacement, one search rule usually corresponds to one replacement result per row. If the rows are misaligned, content intended to be replaced with 111111 might be replaced with another value, so it is recommended to check row by row after configuration.
Step 5: Set the save location and start processing
The wizard flow in the screenshot shows subsequent steps for setting the save location and starting the process. After completing the keyword rule settings, click Next and select the save location for the processed files as needed. To facilitate verification, it is recommended not to directly overwrite important original files, but to save them to a new output directory first. After confirming the replacement results are correct, you can then archive or replace the original files.
Once you enter the Start Processing step, launch the task. The software will process each Excel file in the previously imported list, using the configured wildcard regex rules to find and replace cell text. After processing finishes, open the output files to check if the key columns meet expectations.
Common Questions and Considerations
1. Why should numbers be processed as text?
Many Excel codes look like numbers but are not values used for calculations in a business context. For example, 010039 and 030006 contain leading zeros. If processed as regular numbers, those leading zeros might be lost. The screenshot, where the Text data type is checked, is suitable for handling these types of encoded fields.
2. What content will the rule 030\d{3} match?
It will match text consisting of 030 followed by 3 digits, which are 6-digit numbers starting with 030. For instance, 030006, 030010, and 030999 all match. If your code has a different number of digits, you will need to adjust the digit count accordingly.
3. Can multiple Excel files be replaced together?
Yes. As seen in the screenshot, 3 xlsx files were added to the task list at one time. For dozens or hundreds of tables, you can also use the Import Files from Folder option to reduce the cost of manual selection.
4. Is it necessary to back up files before replacing?
Backup is recommended. Batch replacement is highly efficient, but if a rule is configured incorrectly, the scope of impact can be large. Especially when using wildcards or regex, it's best to first test with a small sample of files before processing all files.
Summary
Using HeSoft Doc Batch Tool , you can batch find and replace rule-based keywords in multiple Excel files in one go. The example in this article used wildcard regex rules like 030\d{3} and 46\d{3} to batch replace multiple codes with specified values, avoiding the tedious manual operations of modifying workbook by workbook and cell by cell.
If you frequently need to process codes, numbers, keywords, or masked data in xlsx and xls tables, it is recommended to delegate these repetitive tasks to a batch processing tool. In practice, you just import files, set the processing scope, find method, and replacement list, then choose a save location and start processing, which can significantly improve office efficiency.