How to batch replace numbers in multiple xlsx sheets? Wildcard matching and unified modification tutorial

Multiple xlsx spreadsheets often contain scattered numbers with the same format but different values, such as a fixed prefix followed by several digits. Enumerating old values one by one is not only inefficient but also prone to omissions. This article starts from the practical needs of Excel data cleaning and demonstrates how to use wildcard regular expression rules to match six-digit numbers starting with 030 and five-digit codes starting with 46, and in HeSoft Doc Batch Tool import multiple workbooks at once, restrict the cell text range, establish lookup-and-replace correspondences, and finally output and check the results uniformly.

When organizing customer lists, asset ledgers, project rosters, region codes, or business archives, enterprises often need to batch-update numbers in Excel. The difficulty is that the old data is not necessarily a single value, but may be a group of values with similar formats. For example, 030006, 030010, and 030148 all need to be changed to a new unified number, but ordinary exact search can only process one specific value at a time. If the same data is also scattered across multiple xlsx files, the amount of manual work will multiply.

For such requirements, a more efficient approach is to first analyze the numbering pattern, and then use wildcards or regular expressions to describe the content to be found. As long as a cell matches the expression, the corresponding replacement value can be applied. This article will use the actual interface to explain how to use HeSoft Doc Batch Tool to perform unified find-and-replace across multiple Excel files. This product is positioned as batch-processing software for office files, suitable for consolidating repetitive file operations into a single task instead of requiring users to open, modify, and save each file one by one.

1. The specific problem this tutorial solves

In the sample workbook, there are two types of data that need to be standardized. The first type is in the Hospital CCN column, where the numbers all start with 030 and are followed by 3 digits. The second type is in the CBSA column, where the target data starts with 46 and is followed by 3 digits. The task requirements are as follows:

• Replace text matching the format of "030 plus 3 digits" with 111111;
• Replace text matching the format of "46 plus 3 digits" with 22222;
• The same set of rules needs to be applied to multiple Excel files;
• Other numbers that do not match the rules should remain unchanged.

If exact search were used, the first type of data would require entering old values such as 030006, 030010, 030011, and 030064 separately. Not only would there be many rules, but numbers not counted in advance could also be missed. By using expressions, you only need to describe the entire set with "030\d{3}", which is also the main value of wildcard batch replacement in Excel data cleaning.

2. Applicable scenarios: which Excel numbers are suitable for rule-based modification

1. Fixed prefix plus serial number

For example, department numbers HR001 and HR002, project numbers XM202501 and XM202502, or 030006 and 030010 in the sample. Such fields usually have a fixed letter or number prefix, and the latter part is a fixed-length serial number, which can be matched at once through an expression.

2. Region code and classification code migration

After organizational adjustments, system upgrades, or changes in coding standards, old region codes may need to be mapped to new unified codes. As long as the old codes have clear patterns in prefix, length, or character composition, they can be found in batch first and then replaced with specified values.

3. Standardization of tables from multiple sources

Excel templates submitted by different branches or suppliers may contain the same fields, but the file names and numbers of data rows differ. Importing these files into the office software at once and processing them with the same set of rules helps avoid inconsistent standards caused by manual modification by different personnel.

4. Data cleaning of historical xlsx ledgers

When a folder accumulates a large number of monthly, quarterly, and annual ledgers, old numbers may exist in many workbooks. Batch processing can reduce the number of times files need to be opened and saved. However, before processing different Excel formats such as xlsx and xls, you should still confirm how the software reads the files with a small number of documents and keep backups of the original data.

3. Effect preview: before and after batch number modification

Excel data before processing

In the original workbook, the Hospital CCN column shows values such as 030006, 030010, 030011, 030064, 030074, 030085, 030111, 030114, and 030148; the adjacent CBSA column continuously shows 46060. These numbers look visually similar, but the actual content stored in each cell may be different.

The arrows in the screenshot mark the two columns to be processed in this task. If you select cells one by one to modify them, you will need to repeat the input and may easily skip a row. If you use find-and-replace in each Excel file separately, you will need to configure the conditions repeatedly across all workbooks. Therefore, this task is more suitable for establishing a reusable set of matching rules.

Excel data after processing

After batch processing is completed, the various numbers that originally matched the 030-prefix rule are uniformly changed to 111111, and the data matching the 46-prefix rule is uniformly changed to 22222. The yellow highlighted area shows the main changes, while data such as 010039, 010052, 010065, 010079, 010131, and 010175 that do not match the expressions were not replaced.

The result shows that the rules can filter targets based on text structure, rather than simply modifying all cells in an entire column. For real business data, this approach of "processing only content that meets the conditions" is more controllable than overwriting an entire column, but the premise is that the expression is written accurately enough.

4. Complete steps: batch replacement of numbers in multiple xlsx files

Step 1: Open the Excel keyword find-and-replace tool

After starting HeSoft Doc Batch Tool , find "Excel Tools" in the left navigation bar. The right side will display batch-processing functions related to Excel, and the first function is "Find and replace keywords in Excel". Click this function to enter the task page.

This step is used to select the correct processing module. Because the task target is cell text inside workbooks, you should not choose other tools such as file name, folder name, or Excel format conversion. After entering the function, you can see that the task consists of several stages: selecting records, setting processing options, setting the save location, and starting processing.

Step 2: Import multiple Excel workbooks at once

In the first stage, click "Add Files" to manually select several Excel files; if the target files are centrally stored in the same directory, you can also use "Import Files from Folder". In the example, product_list.xlsx, team-participant-list.xlsx, and test.xlsx are imported, and the bottom of the page shows a record count of 3.

The file list displays name, path, extension, creation time, and modification time. Before formal processing, you should carefully check the file paths to avoid adding backup directories, already-processed directories, or irrelevant tables to the task. If a file is selected by mistake, you can remove it using the delete button in the corresponding row; if all files need to be reselected, you can click "Clear". After confirming the records are correct, click "Next".

The expected result is that all files that need to apply the same number replacement rules are included in the task list. For dozens or more tables with similar structures, importing from a folder is usually more time-saving than selecting them one by one.

Step 3: Limit the processing scope to cell text

After entering the "Set Processing Options" page, first configure the "Processing Scope". In the interface, you can see three options: "Cell Text", "Worksheet Sheet Name", and "Text in Shapes on Worksheet". Since this example is to modify the numbers in the Hospital CCN column and CBSA column, check "Cell Text".

Worksheet names and shape text are not the targets of this task, so they do not need to be checked at the same time. Correctly limiting the processing target can reduce the possibility that rules act on titles, graphic annotations, or other positions.

Step 4: Set cell formula and data type

In the screenshot, "Cell formula types to process" is set to "No restriction". If the workbook contains both ordinary cells and cells whose results are produced by formulas, this setting will not first exclude content based on formula properties. The interface also provides "Only process cells containing formulas" and "Only process cells not containing formulas", which can be selected according to the data structure in actual use.

For cells containing formulas, the screenshot selects "Value after formula calculation". This means the search focuses on the final displayed result of the formula, rather than the formula expression itself. If the business goal is to modify formula strings, it should be evaluated carefully and a processing method consistent with the task should be selected.

Under "Cell data types to process", "Text" is checked. Numbers often appear to be entirely numeric, but in order to preserve leading zeros, Excel may save them as text. 030006 in the example is a typical case. If the source data is actually stored as numbers, you need to adjust the data type selection according to the file content; do not judge only by visual appearance.

Step 5: Enable formula-based fuzzy text search

Under "Set Keyword Options" in "Search Method", the interface provides "Exact text search" and "Use formula-based fuzzy text search". If you want to find a completely fixed word, you can choose exact search; this example needs to match a batch of different numbers, so select "Use formula-based fuzzy text search".

Here, "formula-based fuzzy search" performs the role of pattern-based text matching. Users do not need to list every old number item by item, but can construct a wildcard regular expression through the numeric prefix and digit count.

Step 6: Enter search expressions and replacement values

In the left "List of keywords to find", enter two lines. Line 1 is "030\d{3}", and line 2 is "46\d{3}". Here "\d" represents one digit and "{3}" represents three consecutive digits, so the first rule can match six-character texts such as 030006, 030010, and 030148, while the second rule can match five-character texts such as 46060.

In the right "List of replacement keywords", enter 111111 in line 1 and 22222 in line 2. The left and right sides correspond by row number: the first search rule corresponds to the first replacement value, and the second search rule corresponds to the second replacement value.

After entering them, you should check line by line. Focus on confirming whether the backslash, number of digits, and braces in the expressions are correct, and also confirm that the replacement list is not misaligned. When there are many rules, you can organize the mapping list externally first and then copy it into the corresponding lists to reduce manual input errors.

Step 7: Narrow the worksheet or column scope as needed

On the settings page, you can also see switches such as "Worksheet names need to meet this condition" and "Cell column names need to meet this condition". In the sample screenshot, these conditions are not enabled, meaning the task does not further limit specific worksheet names or column names.

If the same format of numbers may also appear on other worksheets in a workbook, and that data should not be modified, you should consider enabling the corresponding conditions to narrow the scope. Conversely, if the target numbers in all worksheets need to be processed uniformly, there is no need to add unnecessary restrictions. Scope conditions should be determined based on the file structure, rather than enabled blindly.

Step 8: Set the result save location

After completing the processing options, click "Next" and follow the top workflow to enter "Set Save Location". Before starting a batch task, it is recommended to choose an easily identifiable new directory to store the results, keeping the processed files separate from the original files. Especially when using a new expression for the first time, it is not recommended to keep only one file and overwrite it directly.

You can include "replaced", "new number", or the processing date in the output directory name for easier later verification. After confirming the save location, proceed to the "Start Processing" stage.

Step 9: Start processing and spot-check the output files

After starting the task, the software will process the workbooks in the file list according to the configured rules. When it is finished, do not only check whether the task has ended; you should also open the output files to verify the data.

It is recommended to check at least the following: whether the original 030006, 030010, and similar values have become 111111; whether the original 46060 has become 22222; whether non-matching values such as 010039, 26620, and 10760 remain unchanged; and whether the worksheet structure and other business fields are normal. Only after the spot check passes can the results be used for subsequent aggregation or system import.

5. How to write safer Excel regular expression matching rules

1. First determine the fixed part

The fixed part is an important basis for distinguishing target data. This example uses 030 and 46 as prefixes. If you only write "any number of digits", it may match dates, amounts, quantities, or other numbers in the workbook, making the scope too broad.

2. Then determine the type of variable characters

In this example, the variable parts are all digits, so "\d" is used. If the data also contains letters, spaces, or hyphens, you should first examine actual samples and then write the rules according to the expression method supported by the software, rather than directly applying a digit-only rule.

3. Accurately limit the character count

"{3}" represents three consecutive characters. Limiting the digit count can avoid mixing codes of different lengths in the same process. If the business contains both 03001 and 030001, they should be analyzed separately, and you should not assume by default that they belong to the same type of number.

4. Verify with a small sample

Even if an expression appears syntactically correct, results may differ because of cell data types, hidden characters, or inconsistent number lengths. The most reliable way is to copy one workbook as a test file, run the process, and check the matched and unmatched content item by item.

6. Common questions and precautions

Why were some numbers that look the same not replaced?

Possible reasons include different cell types, spaces in the number, lost leading zeros, actual digit counts inconsistent with the rule, or the target content being outside the selected processing scope. You can click the cell and check the actual content in the formula bar, then adjust the rule.

Why did one rule replace too much data?

This is usually because the rule lacks a fixed prefix or digit-count limit. You should try to describe the target number with a complete structure and, according to the actual situation, restrict worksheet names, column names, and data types. Generally, the more specific the rule, the lower the risk of false matches.

Must the find list and replacement list have the same number of entries?

To ensure that each search rule has a clear replacement result, it is recommended that the left and right lists correspond row by row and remain equal in number. If a row does not need to be replaced, it should be deleted or reorganized before execution, rather than keeping a blank mapping of unknown purpose.

How should cells containing formulas be selected?

If you only want to find the final result displayed by formulas, you can refer to the screenshot and select "Value after formula calculation"; if the target is related to the formula string, you must first confirm whether modifying the formula will affect the calculation logic. For important reports, it is preferable to test on a copy first.

How can I prevent unrecoverable results after batch replacement?

Keep the original files, set a separate output directory, and process a small sample first. While batch processing improves efficiency, it also amplifies the impact of incorrect rules, so backup and spot-checking are steps that cannot be omitted.

7. Summary

When old numbers in multiple Excel or xlsx files have fixed prefixes, character types, and length patterns, there is no need to enumerate and modify them one by one. Through wildcard regular expressions such as "030\d{3}" and "46\d{3}", you can describe the entire group of target data at once, and then replace the matched results with 111111 and 22222 respectively.

HeSoft Doc Batch Tool integrates file import, processing scope, formula type, data type, keyword rules, and save location into the same wizard, making it suitable for highly repetitive office file tasks. It is recommended to first select one or two xlsx copies to complete testing, confirm that the matching and replacement results are accurate, and then import all tables from the folder for batch execution. This can shorten repetitive operation time and also help ensure that multiple files adopt consistent data modification standards.