This article uses a practical example to explain how to batch find and replace keywords that follow a pattern across multiple Excel spreadsheets. Before processing, the spreadsheets contain Hospital CCN numbers starting with 030 and CBSA codes starting with 46; after processing, these contents are replaced with 111111 and 22222, respectively. The article explains, following screenshot steps, how to select the function, add xlsx files, set the cell text range, enable formula fuzzy text search, and fill in replacement rules in HeSoft Doc Batch Tool .
When dozens of content items need modification in an Excel file, manual find and replace can still manage; however, if similar content exists across multiple Excel files, multiple worksheets, and multiple columns, manual processing becomes extremely inefficient. This is especially true for ID-type fields, which often share the same prefix but have different suffixes, such as 030006, 030010, 030064. Simply entering a fixed keyword can hardly cover them all at once.
This article focuses on batch find and replace of keywords in Excel, highlighting how to use wildcard regular expression concepts to quickly modify rule-based content across multiple spreadsheet files. The example uses HeSoft Doc Batch Tool , an office-oriented batch file processing software, which is suitable for reducing repetitive tasks and improving Excel file processing efficiency.
Applicable Scenarios
You can consider using the method in this article if the following situations occur in your work:
- Multiple Excel files contain keywords with the same rules that need unified replacement.
- The keywords are not fixed strings but a type of regular ID or code number.
- You need to process text-based IDs in xlsx spreadsheets, such as hospital IDs, product codes, or project numbers.
- You need to batch-replace some sensitive IDs with masked values before sending out reports.
- Manually opening files and performing find and replace one by one takes too long.
Compared to single-file manual modification, the advantage of a batch processing tool is its ability to add multiple files into a single task and automatically apply the same set of rules to all files. For those who regularly organize weekly reports, monthly reports, customer lists, and business ledgers, this saves a significant amount of repetitive operation time.
Effect Preview: From Original IDs to Unified Replacement Values
Before Processing: Multiple Groups of Rule-Conforming Keywords in Excel
In the pre-processing screenshot, the Excel spreadsheet displays a participation list. Column B is titled Hospital CCN and contains IDs like 010039, 030006, 030010; Column D is titled CBSA and contains codes like 26620, 46060. The red arrows highlight the IDs starting with 030 and the area containing the 46060 code.
The difficulty here is that the values needing processing in column B are not identical but all conform to the pattern of 030 followed by three digits; the value needing processing in column D can also be summarized by 46 followed by three digits. Manually replacing them one by one is not only slow but also likely to miss several rows.
After Processing: Matched Content Replaced According to Rules
The post-processing screenshot shows that the IDs in column B originally starting with 030 have been uniformly replaced with 111111, and the original 46060 in column D has also been replaced with 22222. This achieves the effect of batch finding by rule and then replacing uniformly.
From the results, it is clear that wildcard regular replacement is suitable for processing similar but not identical text. As long as the rules are set accurately, the software can automatically identify the target content without needing to list every original value manually.
Operation Steps
Step One: Select the Excel Batch Find and Replace Function
After opening HeSoft Doc Batch Tool , select Excel Tools on the left side. The main interface will display several batch processing functions related to Excel. In this example, you need to click find and replace keywords in Excel, whose description is batch find and replace keywords in Excel file content.
The expected result of this step is entering the keyword replacement task page. Since this function is specifically designed for replacing Excel file content, you can follow the wizard to complete file selection, processing options, save location, and start processing.
Step Two: Add the Excel Files to Be Modified to the Task List
After entering the task page, the first step is to select the records needing processing. You can select specific Excel files by adding files, or batch add them by importing files from a folder. The screenshot shows the task list already contains three xlsx files, named product_list.xlsx, team-participant-list.xlsx, and test.xlsx.
The list displays information like sequence number, name, path, extension, creation time, and modification time. After adding, confirm the file paths are correct to avoid adding irrelevant spreadsheets to the task. If a row does not need processing, use the delete icon in the operation column to remove it. After confirming the record count and files are correct, click Next.
Step Three: Set the Processing Scope to Cell Text
After entering the processing options settings page, first focus on the processing scope. In the screenshot, Cell Text is checked, indicating the software will find and replace keywords in Excel cell content. The Hospital CCN and CBSA fields needing processing in this example are both located in cells, so this selection is correct.
If your target content is in worksheet names or shape text, you need to select the corresponding scope based on the actual situation. However, this example only processes cells, so the scope is not extended to other objects. In batch replacement, the more specific the processing scope, the more it helps reduce unnecessary matches.
Step Four: Select Text Data Type Based on ID Characteristics
In the screenshot, the cell data type needing processing is selected as Text. Many IDs are composed of numbers but are not numerical values used for calculation; rather, they are business codes. For instance, if 030006 were understood as a regular number, it might be converted to 30006, causing the leading zeros to be lost. Therefore, when processing such Excel keywords, treating them as text better aligns with their actual business meaning.
The page also displays the handling method for cells containing formulas, with the current selection being the value after formula calculation. This means if a cell contains a formula, the processing will be based on the display value calculated by the formula. This helps process spreadsheet content containing formula results.
Step Five: Enable Use Formula to Find Text Fuzzily
In the keyword settings options, select Use formula to find text fuzzily as the search method. This option is suitable for matching a category of text by rules, rather than finding only one completely fixed word. For the IDs starting with 030 and codes starting with 46 in this article, using fuzzy search is more appropriate.
In the left keyword list to search for, enter:
- 030\d{3}
- 46\d{3}
In the right replacement keyword list, enter the corresponding:
- 111111
- 22222
You can understand 030\d{3} as 030 followed by three digits, which can match 030006, 030010, etc.; 46\d{3} means 46 followed by three digits, which can match 46060, etc. The left and right lists correspond row by row, so rule 1 will replace with 111111, and rule 2 will replace with 22222.
Step Six: Continue to Set the Save Location and Start Processing
After setting the search rules and replacement results, click Next to go to the save location settings. To conveniently check the results, it is recommended to save the processed files in a new folder, not mixing them directly with the original files. This way, even if you find the rules need adjustment, you can return to the original files for reprocessing.
Finally, go to the start processing step and execute the task. The software will batch process multiple Excel files according to the file list, replacing keywords matching the rules with the specified content. After processing is complete, you can open the output files and compare the key columns before and after processing to confirm if the expected outcome is achieved.
Common Questions and Precautions
1. When is it appropriate to use wildcard regular expressions instead of simple find and replace?
When the content to be replaced is completely identical, simple find and replace is sufficient; when the content shares the same structure but has varying specific values, such as the first three digits being fixed and the last three changing, using wildcard regular rules is more suitable. 030\d{3} in this article is a typical example.
2. Will batch replacement affect other columns?
If content matching the rules also exists in other columns, it may be matched as well. Therefore, you should check whether the rules are precise enough before formal processing. If you only wish to process certain columns, you can combine this with relevant condition settings in the interface to limit the scope, but whether to enable this depends on the actual file structure.
3. Can the replacement list be left blank?
The prompt in the right area of the screenshot states that leaving it blank means deletion. That is, if a replacement result is left empty, it might mean deleting the matched content. If your goal is to replace it with a specified value, be sure to fill in the corresponding content on the right side.
4. Why is it recommended to process a copy first?
Batch processing is efficient, but its impact scope is also large. Especially when using rule matching, it is recommended to copy a set of files first or choose a new save location, and check the sample results. After confirming there are no mismatches from the rules, apply them to more Excel files.
5. What should be noted when processing xlsx and other Excel files?
The example files in the screenshot have the xlsx extension. In actual office work, you might also encounter formats like xls and xlsm. Before processing, confirm the files can be imported normally, and choose the appropriate data type and processing scope based on the table content.
Summary
This article demonstrated how to use wildcard regular expressions to batch find and replace keywords across multiple Excel spreadsheet files. Using HeSoft Doc Batch Tool , users can first import multiple xlsx files, then select the cell text processing scope, enable Use formula to find text fuzzily, enter rules like 030\d{3} and 46\d{3}, and set the corresponding replacement values.
This method is suitable for batch modifying IDs, unifying codes, data masking, and report cleaning. Compared to manual operation on files one by one, batch processing can significantly reduce repetitive labor and improve accuracy. It is recommended to first verify the rules with a small sample in practice, then batch process all files, making Excel keyword replacement more efficient and controllable.