How to batch replace multiple Excel files by numbering rules? Regex matching and data masking methods

Customer numbers, institution codes, or region codes in multiple Excel files often have different contents but share the same prefix and digit count. For this type of data, entering each complete number individually for replacement is inefficient. This article uses an xlsx workbook example to explain how to batch import files in HeSoft Doc Batch Tool , limit the cell text processing range, use formulas such as 030\d{3} and 46\d{3} for fuzzy matching rules, and replace the two types of codes with specified values respectively. The article also explains the effects before and after processing, the row-by-row correspondence of rules, differences in data types, risks related to formula cells, and testing and backup methods before formal processing.

During data masking, system migration, test environment setup, and report standardization, it is often necessary to modify numbers in a batch of Excel files. The difficulty is that these numbers are usually not exactly the same fixed text, but rather "the same prefix with different trailing digits." For example, one workbook may contain institution numbers such as 030006, 030010, and 030064 at the same time, while another workbook may contain even more codes starting with 030. If you copy and replace the numbers one by one, it is not only slow, but you may also miss some because there are many files and many worksheets.

This article introduces a processing method more suitable for batch office work: in HeSoft Doc Batch Tool , use the "Find and replace keywords in Excel" feature to describe number patterns with wildcard regular expressions, and then process multiple xlsx files at once. The example will replace content starting with 030 and followed by three digits with 111111, and replace content starting with 46 and followed by three digits with 22222. After reading, you can understand the complete workflow and key verification points for this type of batch Excel number replacement task.

1. Applicable scenarios: number replacement, code migration, and Excel data masking

This method is suitable for scenarios where the target content has obvious patterns, and is especially suitable for the following work:

• Before sending Excel lists externally, uniformly hide institution numbers, customer numbers, or internal business codes;
• When switching from an old system to a new system, batch convert old codes in multiple workbooks into new codes or temporary placeholder values;
• When preparing training materials, demonstration data, and test samples, replace real numbers with virtual numbers;
• Multiple departments maintain xlsx ledgers separately and need to uniformly modify a certain type of prefix code;
• A large number of reports are saved by quarter, month, or region, and the same set of replacement rules needs to be executed across files.

It should be noted that the example demonstrates replacing cell text according to rules. Whether data masking meets actual security requirements also depends on the replacement rules, output file management, and organizational policies. For sensitive data, attention should not be limited to "whether it has been replaced"; it is also necessary to check whether there are omissions, hidden worksheets, or other locations that still retain the original information.

2. Effect preview: number changes before and after rule-based replacement

1. Before processing: similar numbers have different content but consistent format

In the original Excel worksheet, the Hospital CCN column contains multiple six-digit numbers. In the marked area of the screenshot, you can see content such as 030006, 030010, 030011, 030064, and 030074. Their last three digits are different, but they all conform to the structure of "030 plus three digits." The CBSA column contains 46060 consecutively, which belongs to the structure of "46 plus three digits."

If exact search is used, every CCN number needs to be listed separately. After using formula-based fuzzy search, you only need to use one expression to describe the same type of number, which can cover multiple different values.

2. After processing: different categories are replaced with their respective target values

In the output workbook, cells that originally matched the 030 number pattern are uniformly displayed as 111111, and cells that originally matched the 46 number pattern are uniformly displayed as 22222. Other rows that do not match the rules still retain their original values, and the hospital names and region names are not changed by this rule.

From the results, it can be seen intuitively that the two types of numbers use two independent find-and-replace mappings. To achieve this effect in a batch task, the key is to select the correct processing scope, enable formula-based fuzzy search, and ensure that the rules on the left and right sides correspond row by row.

3. Operation steps: create a multi-file number replacement task

Step 1: Select the corresponding function in the Excel tool

Start HeSoft Doc Batch Tool , and in the left navigation bar, enter "Excel Tools". The tool's main panel contains multiple functions such as Excel passwords, image export, and format conversion. This time, you should select the first item, "Find and replace keywords in Excel".

After entering the correct function, four task stages will appear at the top: select the records to process, set processing options, set the save location, and start processing. This step-by-step process allows the file list, matching rules, and output location to be confirmed separately, reducing errors caused by mixed operations on the same page.

Step 2: Add multiple Excel workbooks at once

At the file selection stage, use "Add Files" or "Import Files from Folder" according to how the files are stored. If you only need to process a few specified reports, you can use Add Files; if the files to be processed are stored together in one directory, importing from a folder is more suitable for batch tasks.

The example list already contains three xlsx files: product_list.xlsx, team-participant-list.xlsx, and test.xlsx. The page also lists the disk path, extension, creation time, and modification time of each file, and the total record count at the bottom is 3.

Do not rush to click next at this stage. You should first confirm whether old backups, already processed result files, or tables unrelated to the task are included. If there is a mistake, you can remove the file through the delete action on the corresponding row; if you need to reselect all files, you can click "Clear". After confirming the list, proceed to the next step.

Step 3: Limit the processing target to cell text

In the Excel options area, the processing scope includes "Cell text", "Worksheet sheet names", and "Text on shapes in the worksheet". In this example, only "Cell text" is selected because the numbers are located in table cells. If other scopes are also selected, the matching rules may affect sheet names or shape text, increasing unnecessary processing scope.

In the screenshot, the worksheet name condition and cell column name condition are both turned off, so the rules can search in cells that meet other conditions. If the formal task only allows modification of specific worksheets or a specific field, you can further narrow the scope according to the condition options provided in the interface.

For the cell formula type to be processed, select "No restriction"; for the processing method of cells containing formulas, select "Value after formula calculation"; and for data type, select "Text". This set of settings is suitable for the text numbers in the screenshot, but the data storage method may differ in different workbooks. In particular, numbers with leading zeros are usually better saved as text; if Excel recognizes them as numbers, the actual value may no longer contain the leading zero.

Step 4: Enable formula-based fuzzy text search

When setting keyword options, select "Use formula-based fuzzy text search" instead of "Exact text search". Then enter two rows in the "List of keywords to search for":

• 030\d{3}
• 46\d{3}

Here, \d represents one digit, and {3} represents three consecutive occurrences. Therefore, the first rule is used to identify the three digits after 030, and the second rule is used to identify the three digits after 46.

Step 5: Fill in the replacement target content row by row

In the "Replacement keyword list" on the right, enter 111111 in the first row and 22222 in the second row. The row numbers in the left and right lists represent the corresponding relationship:

• 030\d{3} corresponds to 111111;
• 46\d{3} corresponds to 22222.

If the two replacement values are reversed vertically, the final output will also be executed according to the wrong mapping. Therefore, before continuing with a multi-rule task, the rows should be checked one by one. The middle of the screenshot also shows operation icons for adjusting the list contents. In actual use, the final left and right row numbers should prevail.

Step 6: Select the output location and execute processing

After completing the rule settings, click "Next" at the bottom to enter the save location settings. It is recommended to create an independent output directory for this task and use a folder name that explains its purpose, such as "Number replacement results" or "Masked test output". This preserves the original workbooks and also facilitates later comparison.

After continuing to the "Start processing" stage, start the task according to the prompts on the software page. After batch processing is completed, do not only confirm that the files have been generated; you should also open the result files to verify the content. It is recommended to check one original number starting with 030, one code starting with 46, and one piece of data that should not be replaced, and confirm that all three types of results meet expectations.

4. How to write rules according to your own number format

The expressions in the example are not fixed templates, but are written according to the actual number structure. When analyzing the rules, you can first split the number into fixed parts and variable parts. For example, 030006 can be split into the fixed prefix 030 and the variable three-digit number 006, so it is written as 030\d{3}.

If the business numbers have the same prefix but the number of digits is not uniform, you should not directly copy {3}. First count the actual data length, and then design the expression. For codes that may contain letters, hyphens, spaces, or other characters, you should also confirm the syntax of the formula-based fuzzy search used by the software before setting it.

The broader the expression, the more data it covers, but the higher the risk of false matches. The stricter the expression, the higher the accuracy usually is, but it may also miss records with abnormal formats. A reliable approach is to first extract samples from a small number of representative files, test the matching results, and then expand the file scope.

5. Common issues and precautions

1. Why leading zeros affect matching

030006 retains the leading 0 when it is text; if it is saved by Excel as a numeric value, the display format and the actual value may be different. Regular expression rules match the content read by the software, so you need to check the actual data type of the cell rather than judging only by the interface display.

2. Why some content is still not processed although only text is selected

Unprocessed cells may be numbers, logical values, date-time values, or formula results, and may also contain invisible spaces. You can view the cell format and formula bar in the original workbook, and retest in combination with the data type options in the tool.

3. Can two types of numbers be replaced with different results

Yes. This example achieves classified replacement through two rows of search rules and two rows of replacement content. You need to ensure that the quantities and order are consistent, and avoid row misalignment during editing.

4. Should the source files be overwritten

For batch replacement, it is generally not recommended to overwrite the only source files on the first run. Once the expression or scope settings are wrong, multiple workbooks may be affected at the same time. Keeping the original files and outputting to a new directory is a safer approach.

5. What else should be checked after data masking

In addition to the body cells, you should also check other worksheets, formula references, comments, shape text, and whether the file names contain sensitive information according to the actual files. The screenshot in this article only demonstrates cell text replacement, and it cannot be inferred from this that other locations have already been automatically masked.

6. Summary

For Excel content where "the numbers are different but the format is the same", wildcard regular expressions are more efficient than exact search one by one. HeSoft Doc Batch Tool can put multiple xlsx files into the same task and uniformly process cell text through formula-based fuzzy search rules. This example uses 030\d{3} and 46\d{3} to identify two types of codes, and then replaces them with 111111 and 22222 respectively, clearly demonstrating how to implement multi-rule batch replacement.

When preparing for formal processing, it is recommended to proceed in the order of "back up the original files - import a small number of samples - set rules - check the left-right mapping - output to a new directory - spot-check the results". This can give full play to the value of office software in batch processing files and reducing repetitive work, while also reducing the risks caused by overly broad rules, different data types, or selecting the wrong files.