When multiple Excel files contain a large number of similar IDs, opening each spreadsheet and manually replacing them is very inefficient. This article uses batch replacement of Hospital CCN and CBSA codes as an example to explain how to import multiple xlsx files in HeSoft Doc Batch Tool , select the cell text range, use formulas to fuzzy-find text, and use rules such as 030\d{3} and 46\d{3} to batch-match keywords, then replace them with specified content. This is suitable for data masking, code adjustment, and report standardization.
Many office workers encounter a problem when organizing Excel reports: the content to be replaced is not a single fixed word, but a batch of similarly structured identifiers. For example, certain codes all start with 030, with three different digits following; other area codes all start with 46, also followed by varying digits. If you only use ordinary find and replace, you would have to list every specific identifier, which is a huge workload.
This article introduces a more suitable batch processing method: using wildcard regular expressions to find and replace keywords in Excel files in bulk. With the help of HeSoft Doc Batch Tool , you can add multiple xlsx files at once, set matching rules and replacement results in the same task, and let the software automatically complete the repetitive replacements. This saves time and reduces the risk of manual omissions or incorrect changes.
Applicable Scenarios
Bulk replacement of Excel keywords using wildcard regular expressions is particularly suitable for the following office scenarios:
- Data Masking: Replacing rule-based content like customer IDs, hospital IDs, employee IDs, and store IDs with unified placeholder values.
- Code Migration: Excel files exported from an old system contain old codes that need to be bulk replaced with new ones.
- Report Cleaning: Tables submitted by different departments contain identifiers with similar formats but different content, requiring unification by rules.
- Multi-file Processing: Needing to process multiple xlsx files simultaneously, or even a large number of Excel files within a folder.
- Reducing Repetitive Work: Avoiding repeatedly pressing Ctrl+H, entering keywords, replacing, and saving in Excel.
The characteristics of this type of task are clear rules, a large number of files, and many repetitive manual operations. The batch processing capabilities of office software can centralize these steps through configuration, allowing users to spend their time on rule confirmation and result review, rather than mechanical clicking.
Effect Preview: Excel Changes Before and After Replacement
Before Replacement: Identifiers Scattered in Different Columns
In the Excel file before processing, column B, Hospital CCN, contains multiple identifiers starting with 030, and column D, CBSA, contains codes like 46060. The screenshot's red arrows point to the cell areas needing batch processing. You can see these values are not identical fixed words, but a group of texts conforming to a certain pattern.
If you only replace the value 030006, others like 030010, 030011, 030064, etc., will still exist; if you replace all identifiers one by one, you need to compile a complete list, which takes a lot of time. Therefore, a more appropriate approach is to use rule-based matching.
After Replacement: Qualifying Identifiers Unified into Target Values
After processing, identifiers matching the pattern of 030 followed by 3 digits in column B are replaced with 111111, and codes matching 46 followed by 3 digits in column D are replaced with 22222. The screenshot intuitively shows that multiple cells have been replaced according to the rules.
The advantage of this processing method is that users do not need to list every specific identifier in advance. As long as the identifier pattern is confirmed, a single rule can cover a batch of content. It is very practical for bulk replacing Excel identifiers, bulk modifying xlsx keywords, and batch cleaning table data.
Operating Steps
Step 1: Open the Keyword Replacement Function in Excel Tools
After launching HeSoft Doc Batch Tool , select Excel Tools in the left navigation bar. On the right, multiple Excel batch processing functions will be displayed, including Add Password Protection, Remove Password Protection, Export Images, Format Conversion, etc. What you need to use this time is Find and Replace Keywords in Excel.
Clicking this function will lead you to a dedicated wizard page. The operational goal here is very clear: batch find keywords in the content of Excel files and write them back to the output files according to the replacement content you set.
Step 2: Import Multiple Excel Files to Be Processed
After entering the function, the first step is to select the records to be processed. The upper right area of the interface provides operations like Add Files, Import Files from Folder, Clear, More, etc. In the screenshot, 3 files have been imported: product_list.xlsx, team-participant-list.xlsx, test.xlsx. The list also shows the file path, extension, creation time, and modification time, making it easy to confirm if the files are correct.
For tasks with only a few files, you can directly click Add Files; if the number of files is large, using Import Files from Folder will be more efficient. After importing, check the record count in the summary at the bottom. In this example, the record count is 3, indicating the subsequent rules will be applied to these 3 Excel files.
Step 3: Select Cell Text as the Processing Scope
Clicking Next leads to the Set Processing Options page. The screenshot shows that Cell Text is checked in the Processing Scope. Because this example needs to replace identifiers within Excel table cells, this option must be selected.
You can also see processing scope options on the interface, such as Sheet name in the worksheet, Text on shapes in the worksheet, etc. This text's example does not involve processing sheet names or shape text, so they do not need to be checked. When setting the scope, it is recommended to follow a principle: only select the objects that truly need processing. The more precise the scope, the less the risk of accidental replacement.
Step 4: Confirm Settings Related to Formulas and Data Types
On the same page, the software provides options like Cell formula types to be processed, How to process cells containing formulas, Cell data types to be processed, etc. In the screenshot, the formula type is selected as Not Limited; how to process cells containing formulas is selected as Calculated Value of Formula; and the data type checked is Text.
These settings seem like details, but they are very important for bulk replacing Excel keywords. Especially identifiers with leading zeros should usually be treated as text. If 010039, 030006 are treated as numbers, format changes might occur. For coding-type, identifier-type, and account-type fields, it is safer to prioritize processing them as text.
Step 5: Use Formula for Fuzzy Text Search and Fill in Rules
In the Set Keywords Options area, the search method is set to Use Formula for Fuzzy Text Search. Then, enter rules in the Keywords to Find list on the left, and enter replacement results in the Keywords After Replacement list on the right. The settings in the screenshot are as follows:
- Row 1 Find: 030\d{3}, Replace with: 111111.
- Row 2 Find: 46\d{3}, Replace with: 22222.
Here, 030\d{3} can match 030 followed by 3 digits, which means content like 030006, 030010, 030064; 46\d{3} can match 46 followed by 3 digits, which means content like 46060. Through this rule-based writing method, many similar keywords can be covered at once.
When setting, note that the find list and replace list correspond by row. The rule in Row 1 corresponds to the replacement result in Row 1, and the rule in Row 2 corresponds to the replacement result in Row 2. If the right side is left empty, the interface text prompts that leaving it blank means deletion, so if you are replacing rather than deleting, be sure to fill in the target content.
Step 6: Continue Setting Save Location and Execute the Task
After completing the rule settings, click Next. Following the wizard prompts, you will need to set the save location, then proceed to Start Processing. It is recommended to save the processing results to a separate folder, so the original files are kept for comparison. If the results are confirmed to be correct, the new files can be used for subsequent submission or archiving.
After starting the process, the software will process the Excel files one by one according to the imported list. When processing is complete, open the output xlsx files and check if the key columns have been replaced as expected. Focus on checking areas that originally contained identifiers starting with 030 and codes starting with 46 to confirm the results match the example effect.
Common Issues and Precautions
What is the impact of writing rules incorrectly?
The matching scope of a wildcard regular expression depends on the rule itself. If the rule is written too broadly, it might match content that shouldn't be replaced; if it is written too narrowly, it might miss the target content. It is recommended to first test with a small number of sample files, and after confirming correctness, proceed to batch process all files.
Why is the replacement result 22222 one digit shorter?
The content after replacement can have a different number of digits from the original content. In the example, 46\d{3} matches a five-digit code, and the replacement result is 22222, which is also five digits. For the six-digit identifier matched by 030\d{3}, it is replaced with 111111. In actual use, the replacement value should be filled in according to business rules.
Can xls format be processed?
The imported files in the screenshot example are xlsx files. For other Excel formats, the actual software support and importable files on the interface should be taken as the standard. If your work involves xlsx, xls, and other files simultaneously, it is recommended to first test the import and replacement process on a small number of files.
How to avoid accidentally replacing headers or explanatory text?
If headers or explanatory text also contain the same rules, they might be matched. Before setting rules, analyze the file structure first and try to make the expression more precise. For example, limit prefixes, digit counts, or contextual characteristics. The screenshot also provides toggle positions for conditions like Worksheet Name Condition and Cell Column Name Condition, but whether to enable them should be decided based on the actual data situation.
Summary
The key to bulk replacing rule-based identifiers in Excel tables is to change manual one-by-one searching into rule-based matching. Through HeSoft Doc Batch Tool , you can import multiple Excel files at once, select the cell text scope, use the formula for fuzzy text search, and configure wildcard regular rules and replacement values.
For users who need to batch process xlsx reports, bulk replace keywords, or batch clean identifier data, this method can significantly reduce repetitive labor. It is recommended to first prepare sample files to verify the rules, and then apply the same configuration to more files, ensuring both efficiency and controllable results.