How to use wildcard regular expressions to batch find and replace keywords in multiple Excel files

When the same type of number, code, or keyword exists in multiple Excel or xlsx spreadsheets, opening each file one by one to search and replace is not only time-consuming but also prone to missed or incorrect changes. This article uses batch modification of hospital numbers and region codes as an example to introduce how to use wildcard regular expressions to match a batch of cell text that conforms to a pattern, and then through HeSoft Doc Batch Tool import multiple Excel files at once, set search rules and replacement content, and output the processing results uniformly. It is suitable for scenarios such as data cleaning, code updates, template maintenance, and standardization of historical spreadsheets.

In daily office work, many Excel contents that need to be modified are not exactly the same fixed keywords, but rather a batch of numbers that share a common pattern. For example, some workbooks contain six-digit codes starting with "030", while other cells contain five-digit area numbers starting with "46". If only ordinary exact search is used, each original number needs to be listed one by one; if there are many files, multiple xlsx spreadsheets also need to be opened repeatedly, making the process very cumbersome.

The problem this article solves is: how to use wildcard or regular expression matching rules to batch-find keywords matching specific formats across many Excel files and replace them with specified content. In the example, numbers matching the rule of "030 plus three digits" are uniformly replaced with "111111", and numbers matching the rule of "46 plus three digits" are uniformly replaced with "22222". The entire task is completed through the office software " HeSoft Doc Batch Tool ", and its key value lies in adding multiple files at once and configuring processing rules uniformly, thereby reducing the workload of repeatedly searching and replacing file by file.

1. Which scenarios are suitable for Excel wildcard batch replacement

Wildcards or regular expressions are suitable for processing data where "the content is not exactly the same, but the format shares a common pattern". Compared with exact search for a single word, they can describe a group of content with one rule, so they are especially suitable for batch data cleaning.

1. Batch updating different numbers

For example, multiple Excel workbooks contain numbers such as 030006, 030010, 030011, and 030064. Although the last three digits of each number are different, they all start with 030 and have the same total length. In this case, one expression can match this group of numbers without entering each old number separately.

2. Uniformly processing area codes or classification codes

When the area codes in a table contain similar data such as 46060 and 46140, rules can be created based on fixed prefixes and digit counts. The example uses the form of "46 plus three digits" to find targets and uniformly changes the matching results to new codes.

3. Cleaning up xlsx files submitted by multiple departments

Departments such as finance, HR, procurement, and administration often submit Excel templates with similar structures. If a coding standard changes, manually modifying each file one by one can easily miss old data hidden among a large number of rows and columns. Importing files in batches and executing the same rules can keep the processing approach consistent.

4. Maintaining historical ledgers and data templates

Product numbers, customer codes, region codes, project numbers, and archive numbers may all be adjusted as business standards change. For long-accumulated xlsx and xls workbooks, you can first filter the matching scope according to number patterns and then perform batch replacement. In actual use, you should first verify the rules with a small number of copies and confirm that the format to be processed can be read normally by the software.

2. Effect preview: what changes before and after processing

Before processing: the same type of numbers is scattered in the table

In the Excel table before processing, the Hospital CCN column contains different numbers such as 030006, 030010, 030011, 030064, 030074, 030085, 030111, 030114, and 030148; the corresponding rows in the CBSA column contain 46060. The numbers in these cells are not exactly the same, but they respectively conform to the pre-set number patterns.

As can be seen from the screenshot, the data to be processed is located in ordinary Excel cells. If the built-in Excel function is used to operate file by file, it is necessary to repeatedly enter search conditions, replacement content, and save the file in each workbook. When there are many files, many worksheets, or a large number of data rows, repeated operations will significantly increase inspection costs.

After processing: text matching the rules is uniformly replaced

After the batch processing is completed, the original numbers starting with 030 and matching the specified number of digits are replaced with 111111, and the original numbers matching the rule starting with 46 are replaced with 22222. The yellow areas in the screenshot intuitively show the changed cells, while other numbers that do not match the rules remain unchanged.

This shows that the operation does not modify the entire column indiscriminately, but first finds the cell text that meets the conditions according to the expressions, and then processes it according to the one-to-one corresponding replacement content in the rule list. For tasks that require modifying multiple groups of keywords at the same time, multiple rows of search rules and replacement rules can be maintained in the same process.

3. Steps for batch finding and replacing Excel keywords using office software

Step 1: Enter the "Find and Replace Keywords in Excel" function

Open HeSoft Doc Batch Tool , and select "Excel Tools" from the function categories on the left. After entering the Excel tool list, click "Find and Replace Keywords in Excel". The description of this function is to batch find and replace keywords in Excel file contents, which is consistent with the goal of this article to process multiple workbooks.

The purpose of this step is to enter the task flow specifically for Excel content replacement, rather than using file renaming or format conversion functions. After entering, the page will guide the user through configuration in the order of "select records to be processed", "set processing options", "set save location", and "start processing".

Step 2: Add the Excel files to be batch processed

On the "Select records to be processed" page, you can click "Add Files" to select workbooks, or click "Import Files from Folder" to add multiple files from a directory at once. In the example list, product_list.xlsx, team-participant-list.xlsx, and test.xlsx have already been added, for a total of 3 records.

After the files are added, you should check the names, paths, and extensions in the list to confirm that no unrelated files have been selected by mistake. If a record does not need to be processed, you can use the delete action on the right side of that row to remove it from the list; if you want to reselect, you can also use "Clear" on the page. After confirming that the file list is correct, click "Next" at the bottom.

The expected result of this step is that all Excel files that need to execute the same set of replacement rules appear in the task list. Importing files centrally is the key efficiency advantage of batch processing over opening Excel one by one.

Step 3: Set the Excel processing scope

After entering "Set processing options", first determine the Excel objects to be processed. The "Processing scope" in the screenshot provides options such as "Cell text", "Worksheet Sheet name", and "Text on shapes in the worksheet". This example needs to modify the numbers in table cells, so check "Cell text".

If the actual task is to modify worksheet names or text on shapes, the corresponding scope should be selected according to the goal, and unrelated areas should not be included in the processing at the same time. The more accurate the scope selection, the easier it is to avoid replacing content in unintended locations.

Step 4: Determine the formula type and data type

In the screenshot, "Cell formula type to be processed" is set to "No restriction", which means the search scope is no longer narrowed based on whether formulas are included. For cells containing formulas, the interface also provides two processing methods: "Value after formula calculation" and "Formula expression". This example focuses on the final displayed data, so the screenshot selects "Value after formula calculation".

In "Cell data type to be processed", the example checks "Text". Although the numbers are composed of numeric characters, leading zeros often need to be preserved, such as 030006. If such numbers are treated as ordinary numeric values, leading zeros may have special meaning, so the selection should be made based on the actual data type in the source table. The screenshot also shows optional types such as number, logical value, and date-time, and it is recommended to check them only when the corresponding content really needs to be processed.

Step 5: Select wildcard matching mode and fill in rules

In the "Set keyword options" area, select "Use formula fuzzy search for text" as the search method. This option is used to search for text according to expression patterns and is also the core setting for implementing Excel wildcard batch replacement in this example.

In the "Keyword list to search" on the left, enter two rules: the first is "030\d{3}", used to match text in which 030 is followed by 3 digits; the second is "46\d{3}", used to match text in which 46 is followed by 3 digits. In the "Replacement keyword list" on the right, enter 111111 and 22222 in the same row order.

The correspondence between rules is very important: content matched by row 1 on the left is replaced using row 1 on the right, and content matched by row 2 on the left is replaced using row 2 on the right. Do not insert blank rows at will or misalign them, otherwise the replacement result may be inconsistent with expectations. In the screenshot, "Ignore letter case" is not checked; the current example only processes numbers, so this option will not have a significant impact. If the rules contain English letters, you need to decide whether to enable it based on whether the data is case-sensitive.

Step 6: Set the save location and start processing

After completing the search and replacement rules, click "Next" to enter "Set save location" in the interface flow. It is recommended to save the processing results to a separate directory, and do not directly overwrite the only original file before verifying the rules. After the save location is set, continue to "Start processing", and the software will process the added Excel files in sequence according to the same configuration.

After processing is complete, open the output files for spot checks. Focus on three types of content: whether numbers matching the first rule have become 111111, whether numbers matching the second rule have become 22222, and whether data that does not match any rule remains unchanged. After confirming that the results are correct, apply this set of rules to a larger range of files.

4. How to understand wildcard and regular expression rules

In this example, "\d" represents a digit, and "{3}" means the preceding digit rule appears three consecutive times. Therefore, "030\d{3}" can be understood as 030 followed by any 3 digits, and "46\d{3}" can be understood as 46 followed by any 3 digits. They each describe a type of text rather than a fixed value.

The broader the expression, the more content it matches. For example, writing only a very short prefix may affect data that was not intended to be modified. Before formal batch processing, it is recommended to first confirm the total number length, fixed prefix, column location, and cell data type, and then write sufficiently precise rules.

5. Common problems and precautions

1. Why no numbers starting with 0 are found

Numbers starting with 0 may be stored as text in Excel, or they may be recognized as numbers and lose their leading zeros. You should first check the actual content and data type of the source cells, and confirm that the "Cell data type to be processed" selected in the software is consistent with the source data.

2. Why content that does not need to be modified is also replaced

The common reason is that the expression range is too broad, or too many processing scopes are selected. You can add fixed prefixes, limit the number of digits, and further narrow the scope through interface options such as "The worksheet name needs to meet this condition" or "The cell column name needs to meet this condition", but only enable such options when it is truly necessary to limit worksheets or columns.

3. How to avoid correspondence errors among multiple rules

The search list and replacement list correspond by row number. After filling them in, check row by row, especially blank rows, rule order, and replacement values. You can first run a test file to confirm the result of each rule, and then import all workbooks.

4. Should the original files be overwritten directly

Batch replacement affects multiple files at the same time, and once a rule is set incorrectly, the scope of rework will also expand. A safer approach is to keep a backup of the original files, save the output results to a new folder, and spot-check the data, formulas, and worksheet structure after processing.

5. Are dates and numbers suitable for processing as text rules

This should be determined according to the actual cell type. Date-time, numeric values, logical values, and text are stored differently in Excel. If the target is a number, code, or field with leading zeros, you usually need to pay special attention to whether it is text. Do not check all categories at the same time without understanding the data type.

6. Summary

Using wildcards or regular expressions to batch find and replace Excel keywords can turn the work of "modifying number by number and file by file" into "defining rules once and executing them in batches". HeSoft Doc Batch Tool , as batch processing software for office files, can centrally import multiple Excel files and complete the settings for processing scope, search method, keyword rules, replacement content, and save location through a wizard.

If you are maintaining a large number of xlsx spreadsheets, it is recommended to first copy one or two files as test samples, enter the expressions according to the steps in this article, and verify the processing effect; after confirming that the matching scope is accurate, import the entire folder for batch execution. This can reduce repetitive work while also lowering the risks of missed changes, incorrect changes, and inconsistent processing standards across different files.