Facing duplicate numbers, codes, or sensitive fields that repeatedly appear across multiple Excel files, manual deletion is inefficient and risky. This article, combined with practical operation screenshots, explains how to use the "Find and Replace Keywords in Excel" feature in HeSoft Doc Batch Tool to batch clear Excel cell text by matching six-digit numbers starting with 030 and five-digit codes starting with 46 using regular expressions, and leaving the replacement content empty. Suitable for xlsx report cleaning, data masking, and batch modification of office documents.
Many Excel reports exported from business systems contain a large number of code-like fields, such as customer numbers, institution codes, region codes, and product codes. These are usually not completely identical texts but follow a certain format: the first few digits are fixed, while the latter digits vary. For example, in the Excel table in this case, column B contains multiple 6-digit numbers starting with 030, and column D contains multiple 5-digit codes starting with 46. If you need to delete this content from a batch of xlsx files, relying solely on manual filtering and find-and-replace will be very troublesome.
This article introduces a method more suitable for batch office processing: using HeSoft Doc Batch Tool to search for keywords using wildcard regular expressions across multiple Excel files, and then leaving the replacement result empty to achieve batch deletion. It addresses not small changes in a single table, but the repetitive task of cleaning up a large number of Excel files, suitable for daily office scenarios like administration, HR, finance, operations, and data organization.
Applicable Scenarios: Why Use Regular Expressions for Batch Deleting Excel Keywords
Excel's built-in find and replace is suitable for handling fixed content, such as replacing "Beijing" with "Beijing City". However, if you need to delete a batch of content with similar formats but different values, like 030006, 030010, 030064, 030148, listing the keywords one by one is very time-consuming. Using regular expressions is more appropriate in such cases.
Taking 030\d{3} as an example, it can match all strings starting with 030 and followed by 3 digits; taking 46\d{3} as an example, it can match all strings starting with 46 and followed by 3 digits. This way, you don't need to write out every specific number; just writing one rule can cover all similar content.
Typical applications include:
- Batch deleting a certain type of number in Excel, rather than deleting all numbers.
- Desensitizing multiple xlsx, xls reports by clearing sensitive codes that match rules.
- Organizing historical data by replacing unnecessary field contents with blanks.
- Batch processing Excel files within a folder, reducing repetitive opening and saving operations.
- Modifying only cell text without changing the table's row and column structure.
Effect Preview: From Original Numbers to Batch Clearing
Before Processing: Numbers and Codes Scattered in Different Columns
From the before-processing screenshot, you can see the Excel table contains multiple fields, such as "Mandatory or Voluntary Participant", "Hospital CCN", "Hospital Name", "CBSA", "CBSA Name", etc. In the area pointed to by the red arrow, column B has numbers starting with 030, and column D has codes like 46060. This content appears consecutively in the table, but they are specific values in cells, not entire rows that need deletion.
If processed manually, you would need first to find all numbers starting with 030, then all codes starting with 46, and also confirm whether similar content exists across multiple files. The more files there are, the higher the probability of errors.
After Processing: Cell Contents Matching Rules Are Deleted
The after-processing screenshot shows that cells matching the rules have been cleared. The original 030 series numbers in column B are no longer displayed, and the codes matching the 46-starting rule in column D have also been deleted; while hospital names, city names, and other non-matching numbers are still retained. This is precisely the effect of batch find and replace with empty values.
This processing method is suitable for scenarios where you only want to delete the "keywords themselves". If you just want to erase a certain type of number from the table without deleting entire rows of data, you can adopt this approach.
Operation Steps: Complete Excel Batch Regex Deletion Following Screenshots
Step 1: Open the Find and Replace Function in the Excel Tool
After launching HeSoft Doc Batch Tool , you can see categories on the left side like "Home, Task Flow, All Tools, File Name, Folder Name, File Organization, Word Tools, Excel Tools, PowerPoint Tools, PDF Tools". Select "Excel Tools", then click "Find and Replace Keywords in Excel" on the right function card.
The purpose of this step is to enter the module specifically designed for batch replacement of Excel content. For users who need to process multiple spreadsheet files, it is more direct than opening Excel files one by one.
Step 2: Import Multiple xlsx Files to Be Processed
After entering the function page, the first thing is "Select records to process". You can click "Add Files" to manually select Excel files, or click "Import Files from Folder" for batch import. The screenshot shows 3 files have been imported, all with the xlsx extension, located in the test directory on the D drive.
The "Name, Path, Extension, Creation Time, Modification Time" in the list helps you confirm if the files are correct. If unnecessary files are imported, you can remove them using the delete button in the operation column. After confirming the record count and file names, click "Next".
Step 3: Specify the Processing Scope as Cell Text
On the "Set Processing Options" page, first set the Excel processing scope. The screenshot shows "Cell Text" is checked, while "Worksheet Sheet Name" and "Text on Shapes in Worksheet" are not checked. This indicates that only the content within worksheet cells will be processed in this task.
If your keywords exist in the table data area, selecting "Cell Text" is sufficient. Unless you indeed need to modify sheet names or shape text, it is not recommended to check extra options to avoid expanding the processing scope.
Step 4: Set Cell Options Based on Data Situation
In the screenshot, the "Cell Formula Type to Process" is set to "No Limit", meaning no exclusion based on whether cells contain formulas; "Processing Method for Cells Containing Formulas" is set to "Value after Formula Calculation", meaning if a cell has a formula, the calculated result will be used for matching; "Cell Data Type to Process" is checked for "Text".
In actual office reports, numbers often look like digits but are stored as text to preserve leading zeros. For example, if "030006" is processed as a regular number, the leading zero might be lost. Therefore, when processing such numbers, focusing on the text type is reasonable.
Step 5: Use Formula Fuzzy Search and Enter Regex Rules
In the "Set Keyword Options" area, select "Use Formula Fuzzy Search for Text" as the search method. Then, enter the matching rules line by line in the "List of Keywords to Find". The two rules in the screenshot are:
- 030\d{3}
- 46\d{3}
The first is used to match 6-digit numbers starting with 030, and the second for 5-digit codes starting with 46. Because the "Replacement Keyword List" on the right is not filled in, and the interface prompts "Leaving blank means deletion", the software will replace the matched content with empty values.
Note here that the broader the regex rule, the larger the matching scope. For instance, \d+ might match a large number of digits, which is unsuitable for this specific need of deleting only particular numbers. It is advisable to be as specific as possible, like using a fixed prefix plus a fixed length.
Step 6: Continue Setting Save Location and Start Batch Processing
The flow display at the top of the interface shows that after completing "Set Processing Options", you also need to proceed to "Set Save Location" and "Start Processing". It is recommended to save the processed files to a new folder, which allows you to compare with the original files at any time. If a rule is written incorrectly, it will not directly overwrite the original data.
After finally starting the processing, the office software will automatically traverse the imported Excel files, find content matching the settings, and replace them with empty values. For multiple files, this step can save a significant amount of repetitive operation time.
Common Questions and Precautions
Do Regex Rules Need to List Every Single Number?
No. The advantage of regular expressions is using a rule to represent a category of content. For example, for 6-digit numbers starting with 030, just writing 030\d{3} is enough; you don't need to list 030006, 030010, 030064 all out.
What if You Only Want to Delete Content in a Specific Column?
The screenshot shows a switch for "The cell's column name must meet this condition", but it is currently not enabled. Whether to restrict by column name depends on your actual file structure. If all files have consistent column names, enabling the column name condition can further narrow the processing scope; if unsure, it's recommended to test on a small scale first.
Will Leaving the Replacement List Empty Cause an Error?
The interface clearly prompts "Leaving blank means deletion", so in this function, not filling in the replacement keyword represents clearing the matched content. This suits the requirement of batch deleting keywords perfectly.
Why Test with a Small Number of Files First?
Batch processing is efficient, which also means the rules will quickly apply to all files. If the regular expression is written incorrectly, it might clear out content that should not be deleted. It is recommended to first select 1 or 2 copied test files, confirm the processed effect meets expectations, and then import the entire folder.
Will the Table Format Change After Processing?
Judging from the effect screenshots, the processing target is the cell text content; the table's row and column structure, headers, and other non-matching text are still preserved. Specific format retention may vary; please refer to the actual file processing results, and be sure to back up important files before processing.
Summary: Key to Batch Clearing Rule-Based Excel Content is Writing Accurate Matching Conditions
Using wildcard regular expressions to batch delete Excel keywords, the core steps can be summarized as: import multiple Excel files, select processing cell text, enable the fuzzy search method, enter matching rules, leave the replacement content empty, then set the save location and start processing. Through this method, formatted content like numbers starting with 030 and codes starting with 46 can be cleared at once.
The value of HeSoft Doc Batch Tool lies in concentrating a large amount of repetitive Excel find-and-replace work into a single process. For those who frequently handle xlsx and xls reports, mastering this method can significantly reduce manual operations and improve the efficiency of data cleaning and file organization. It is recommended to start testing the rules from a sample spreadsheet, and once confirmed correct, proceed to batch process the entire folder.