Excel wildcard batch replacement tutorial: Use regular expressions to modify multiple xlsx files at the same time

When multiple Excel workbooks contain regularly numbered items, codes, or keywords with the same patterns, opening files one by one to find and replace is not only time-consuming but also prone to omissions. This article uses the batch modification of hospital CCN numbers and CBSA codes as an example to introduce how to use HeSoft Doc Batch Tool to import multiple xlsx files, set wildcard regular expressions through "Use formula to fuzzy find text", and specify replacement content for different matching rules. By following the workflow described in this article, you can process cell text in multiple workbooks at once, reduce repetitive operations, and improve the accuracy and controllability of batch replacement through before-and-after effect comparisons, rule correspondence, and backup recommendations.

When dozens or even hundreds of Excel workbooks contain numbers, product codes, region codes, or fixed-format keywords that need to be modified, using Excel's built-in find-and-replace function to process them one by one creates a large amount of repetitive work. This is especially true when the target content is not exactly the same but shares a common format, such as all six-digit numbers starting with 030 or all five-digit codes starting with 46. Ordinary exact matching is difficult to cover all of them at once, while manual filtering is prone to omissions.

The problem this article aims to solve is: how to use wildcard regular expressions to find and replace cell contents that match specific patterns across multiple xlsx files at the same time. The office software used in the example is HeSoft Doc Batch Tool , which is positioned as a batch processing tool for office documents such as Word, Excel, PowerPoint, and PDF. In this case, we use its "Find and replace keywords in Excel" feature to add multiple Excel files to the same task, and then complete the batch replacement through formula-based fuzzy matching.

1. Applicable scenarios: Which Excel content is suitable for wildcard batch replacement

Exact find-and-replace is suitable for processing completely identical text, such as replacing all instances of an "old company name" with a "new company name." However, in real business data, much of the content to be processed has the same structure but not exactly the same characters, so fuzzy rules in the form of wildcards or regular expressions are more appropriate.

Common applications include:

• Batch replacing employee IDs, customer IDs, hospital IDs, or project IDs with the same prefix;
• Unified migration of region codes, department codes, and classification codes across multiple Excel reports;
• When preparing outgoing data, uniformly replacing numbers in a certain format with test values or masked values;
• Multiple xlsx workbooks using an old coding scheme that need to be changed to new placeholder codes according to a pattern;
• Templates, monthly reports, ledgers, and archive tables where there are many files and cross-file find-and-replace needs to be completed in one pass.

Compared with opening Excel files one by one, the value of batch processing is not only saving clicks, but also allowing all files to use the same set of find conditions. For tasks with clear rules and a large number of files, this approach is more conducive to maintaining consistent results.

2. Preview of results: What changed before and after processing

1. Excel data before processing

The worksheet before processing contains fields such as Hospital CCN, Hospital Name, CBSA, and CBSA Name. In the screenshot, the Hospital CCN column contains six-digit numbers starting with 030, such as 030006, 030010, 030011, and 030064; the CBSA column contains consecutive five-digit codes starting with 46, such as 46060. The red arrows mark the two data areas that need to be batch processed this time.

These numbers differ in their trailing digits, so it is not possible to enter just one complete keyword for exact replacement. However, they have stable formats: one type is "030 followed by three digits," and the other is "46 followed by three digits." Therefore, separate fuzzy find rules can be written for each.

2. Excel data after processing

After processing is complete, the Hospital CCN numbers that originally matched the rule starting with 030 are uniformly replaced with 111111, and the CBSA codes matching the rule starting with 46 are uniformly replaced with 22222. The yellow areas in the screenshot clearly show the replacement results, while contents that did not match the rules, such as 010039, 010052, and 29300, remain unchanged.

This shows that the rules do not apply indiscriminate replacement to the entire column, but instead find cell text that matches the expressions and then perform the replacement. In actual use, you should still test with a copy first to confirm that the expression coverage matches expectations before processing the official files.

3. Operation steps: Batch find and replace across multiple Excel files

Step 1: Enter the Excel keyword find-and-replace feature

Open HeSoft Doc Batch Tool , and select "Excel Tools" from the function categories on the left. The main interface will display several Excel batch processing features. Find and click "Find and replace keywords in Excel." The description of this feature is to batch find and replace keywords in Excel file contents, which corresponds to this cross-file replacement task.

The purpose of this step is to enter the dedicated Excel content replacement task, rather than using file name replacement or other document tools. After entering, the interface will guide the operation in the order of selecting records, setting processing options, setting the save location, and starting processing.

Step 2: Add the multiple xlsx workbooks to be processed

On the "Select records to process" page, you can click "Add files" to select workbooks one by one, or click "Import files from folder" to add files from the same directory in a batch. In the screenshot example, three files are added at once: product_list.xlsx, team-participant-list.xlsx, and test.xlsx. The list also displays the file name, path, extension, creation time, and modification time.

After adding the files, first check the number of files and paths to avoid adding workbooks that do not need to be modified. If you find that the wrong file was selected, you can use the delete action on the right side of the list to remove it. If you need to reselect, you can also use "Clear" on the page. After confirming that the file list is correct, click "Next."

Step 3: Determine the processing scope and cell type

On the "Set processing options" page, first set the Excel processing scope. In the example, "Cell text" is selected because what needs to be modified is the numbers in worksheet cells, not the worksheet Sheet names or text on shapes.

The page also provides options such as worksheet name conditions, cell column name conditions, cell formula type, how to handle cells containing formulas, and data type. In the example, the worksheet name and column name conditions remain disabled, meaning no additional restriction is applied to a specific Sheet or column; the formula type is set to "Unrestricted"; and the cell data type to be processed is set to "Text." These settings should be adjusted according to the actual files and should not be copied mechanically.

For example, if you only want to process text-based numbers, you should give priority to restricting the type to text; if the numbers are saved as numeric values in the original workbook, you need to reassess based on the actual data type. When cells with formulas are involved, you should also distinguish between the calculated value of the formula and the formula expression to avoid selecting the wrong replacement target.

Step 4: Select formula-based fuzzy find and enter the expressions

In the "Set keyword options" area, switch the find method from "Exact text find" to "Use formula for fuzzy text find." In the screenshot, two rules are entered in the find list:

1. 030\d{3}: Indicates finding text where 030 is immediately followed by three digits, such as 030006, 030010, and 030148;
2. 46\d{3}: Indicates finding text where 46 is immediately followed by three digits, such as 46060.

The "Replacement keyword list" on the right is filled with 111111 and 22222 in the same row order. That is, the first find rule corresponds to the first replacement content, and the second find rule corresponds to the second replacement content.

This is the most critical step in the entire operation. The order of the left and right lists must be consistent; otherwise, CCN numbers may be replaced with the target value intended for CBSA. In the screenshot, "Ignore letter case" is not checked. Since this example processes numbers, the case option will not affect the result. If the rules contain English letters, you should decide whether to enable it based on business requirements.

Step 5: Set the save location and start processing

After confirming the rules, click "Next" to enter "Set save location." To facilitate review and protect the original data, it is recommended to save the results to a separate new directory and not directly overwrite the only original file without a backup. After completing the save location settings, continue to "Start processing" and execute the task according to the on-screen prompts.

After processing is complete, you should at least spot-check one output workbook: search whether 111111 and 22222 have appeared, and check whether numbers that originally did not match the 030 or 46 rules remain unchanged. If the task involves many files, you can also sample and verify different workbooks according to the file list.

4. How expressions and replacement lists should correspond

Batch rules usually use a row-by-row correspondence. The find content in the first row corresponds to the replacement content in the first row, the second row corresponds to the second row, and so on. Therefore, when entering multiple rules, it is recommended to first organize the "find rule - replacement result" mapping in external text, and then copy it into the software interface to reduce the risk of misalignment.

\d is used to represent digits, and {3} means that the preceding digit pattern appears three consecutive times. Therefore, 030\d{3} matches 030 followed by three digits. This expression should not simply be understood as searching only for the three characters "030"; it describes a complete format.

If the actual number length is different, you should adjust the rules accordingly and verify with a small number of test files first. If the expression is written too broadly, it may match data that should not be modified; if it is too narrow, it may miss the target content.

5. Common questions and precautions

1. Why are some seemingly identical numbers not replaced

First check the actual data type of the cells. Content displayed as numbers in Excel may be text, numeric values, or values generated by formulas. The processing settings in the screenshot only selected text, so non-text types may not be processed in the same way. Also check whether there are spaces before or after the numbers, whether leading zeros have already been removed by Excel, and whether the number of digits matches the expression.

2. Can I process only specified worksheets or specified columns

In the interface, you can see two switches: "The worksheet name needs to meet this condition" and "The cell column name needs to meet this condition." If the task requires narrowing the scope, you can set restrictions according to the conditions provided on the page; if you are not familiar with how to write the conditions, it is recommended to test in a copied file first.

3. Why does replacement become misaligned with multiple rules

The most common reason is that the row numbers in the left and right lists do not correspond one-to-one. After adding, deleting, or adjusting rules, you should recheck each row. Do not only check the expressions themselves; also check the corresponding replacement text.

4. Why is it recommended to save to a new directory

A batch task affects multiple files. Once a rule is written incorrectly, the scope of rework will also expand. Keeping the original files and outputting the results to a new directory makes it easier to compare the differences before and after processing, and also makes it easier to reconfigure the rules when problems are found.

5. Can it be used directly on official data

It is recommended to first copy one or two representative workbooks for small-scale testing. After confirming that the match count, replacement results, formulas, and formatting are all normal, add all the files. For financial, medical, customer, and project data, you should also comply with your organization's data management and approval requirements.

6. Summary

Using the "Find and replace keywords in Excel" feature of HeSoft Doc Batch Tool , you can put multiple xlsx workbooks into the same processing task and use formula-based fuzzy text matching to identify numbers with a common format. In this example, the two rules 030\d{3} and 46\d{3} were used to replace different numbers with 111111 and 22222 respectively, avoiding repeated operations on each file and each worksheet one by one.

If you are processing multiple Excel ledgers, business reports, or outgoing data files, it is recommended to back up the files first, select a small sample to verify the expressions, and then batch import all workbooks. After correctly setting the processing scope, data type, and rule correspondence, you can complete Excel wildcard batch replacement more reliably, reducing repetitive work and improving file processing efficiency.