When multiple Excel spreadsheets contain the same type of numbers, codes, or keywords that need to be cleaned up, opening each file to find and replace is not only time-consuming but also prone to missed deletions. This article uses HeSoft Doc Batch Tool as an example to demonstrate how to use the "Find and Replace Keywords in Excel" feature to batch-delete matching content in cells across multiple xlsx spreadsheets using wildcard regular expressions like 030\d{3}, 46\d{3}, while keeping other data unchanged. It is suitable for office scenarios such as number masking, area code cleaning, and deleting sensitive fields from reports.
In daily office work, Excel spreadsheets often carry large amounts of structured data, such as product lists, customer directories, hospital numbers, region codes, financial records, and statistical reports. Often, we don't want to delete a fixed text string, but a category of content sharing a common pattern: for example, all 6-digit codes starting with 030, or all 5-digit area codes starting with 46. If it's just one file, using Excel's built-in Find and Replace might be barely manageable; but if there are dozens or even hundreds of xlsx and xls files to process, opening, finding, and replacing in each one becomes a highly repetitive and error-prone task.
The method introduced in this article uses the office software HeSoft Doc Batch Tool , through its "Find and Replace Keywords in Excel" feature, combined with wildcard or regular expression rules, to batch delete matching keywords from multiple Excel files at once. "Delete" here doesn't mean removing entire rows or columns, but replacing the matched cell text with nothing, thereby clearing the specified codes or keywords while preserving the spreadsheet structure, other column contents, and unmatched data.
As seen in the screenshot, the sample data requires the deletion of two types of content: one is entries in the Hospital CCN column like 030006, 030010, 030011; the other is codes in the CBSA column like 46060. By setting the expressions 030\d{3} and 46\d{3}, the software can automatically identify and batch-clear these patterned texts.
Applicable Scenarios: Which Excel Batch Keyword Deletion Tasks Suit This Approach
Using wildcard regular expressions to batch delete Excel spreadsheet keywords is suitable for solving office problems where the content has a pattern but a large volume. Compared to manual filtering and replacement, the greatest value of this method is that you don't need to worry about how many specific values exist; as long as the rules are correct, the software can automatically find and process them across multiple files.
Common applicable scenarios include:
- Batch deleting data with the same code prefix: For example, to delete all codes starting with 030 followed by 3 digits, you can use an expression like 030\d{3}.
- Batch clearing area codes, department codes, project codes: For example, to delete all 5-digit codes starting with 46, you can use a rule like 46\d{3}.
- Batch deleting sensitive fields: Such as phone number segments, ID number segments, customer codes, internal employee IDs, etc. As long as the field has a fixed format, it can be located by expression.
- Batch processing multiple Excel files: Including common Excel spreadsheet files like xlsx and xls. The screenshot shows three files imported at once: product_list.xlsx, team-participant-list.xlsx, and test.xlsx.
- Data masking before report distribution: Before sending out spreadsheets, delete codes or identifiers that should not be public, retaining displayable information like names, classifications, and regions.
If your requirement is to "replace certain matching content with blanks" rather than manually deleting entire columns or rows, then this type of batch find-and-replace tool is very suitable. It can reduce repetitive labor, lower the probability of missed processing, and is more suitable for standardized, batch office workflows.
Effect Preview: Excel Spreadsheet Changes Before and After Processing
Before Processing: The spreadsheet contains codes and identifiers to be batch-deleted
In the Excel file before processing, the Hospital CCN column contains multiple codes starting with 030, such as 030006, 030010, 030011, 030064, 030074, etc.; the CBSA column also contains multiple entries of 46060. The screenshot highlights these areas with red arrows, showing content with a clear pattern, which is very suitable for batch locating using wildcards or regular expressions.
If processed manually, one would need to first filter or search for codes starting with 030, then search for codes starting with 46, and also ensure all processing is complete across multiple files. With many files, the operation is not only slow, but it is also difficult to guarantee that every workbook and worksheet has no omissions.
After Processing: Content matching the expression is cleared, other data is retained
After processing is complete, the codes and identifiers that originally matched the rules are deleted, and the relevant cells become blank. The screenshot shows that in the Hospital CCN column, codes starting with 030 have been cleared, and 46060 in the CBSA column has also been cleared; while data in other columns like Hospital Name and CBSA Name is still retained. This demonstrates that this processing targets the cell text content and does not damage the spreadsheet's row and column structure.
For Excel files that need batch masking, batch code clearing, or batch keyword deletion, this result is often exactly what we want: only delete text matching the rules, without affecting other fields, allowing continued filtering, statistics, or distribution after processing.
Operation Steps: Using HeSoft Doc Batch Tool to Batch Delete Excel Keywords
The following explains the actual workflow according to the order of the screenshots. The overall process can be understood in four steps: select the tool, import Excel files, set find-and-replace rules, and choose the save location to start processing. The software uses a wizard-style interface, suitable for batch processing multiple files.
Step 1: Enter the Excel tool and select "Find and Replace Keywords in Excel"
After opening HeSoft Doc Batch Tool , select Excel Tools in the function category on the left. Find and click "Find and Replace Keywords in Excel" in the function list. In the screenshot, this function is located as the first item in the Excel Tools list, indicating it is specifically designed for batch finding and replacing content in Excel files.
The purpose of this step is to enter the processing module suitable for this task. Since our goal is to delete keywords from many Excel spreadsheet files, which essentially means replacing matching keywords with nothing, we should choose a "Find and Replace" type function rather than format conversion, image export, or encryption/decryption functions.
Step 2: Add the Excel files to be processed in batch
After entering the function page, you first arrive at the "Select records to process" step. In the upper right area of the interface, you can see buttons like Add File, Import Files from Folder, Clear, and More. According to the screenshot, three xlsx files have been imported currently: product_list.xlsx, team-participant-list.xlsx, and test.xlsx. The table displays information such as file path, extension, creation time, and modification time.
If the files are scattered in different locations, you can use "Add File" to select them individually; if a batch of Excel files is stored in a single folder, using "Import Files from Folder" is more suitable. After importing, it is recommended to first check the file names and extensions in the list to confirm no unnecessary spreadsheets were selected by mistake. The bottom of the interface shows the record count as 3, indicating that the same set of find-and-replace rules will be applied to these 3 Excel files.
The expected result of this step is that all Excel files where keywords need to be deleted are present in the pending list. Only files in the list will have the expression rules set later applied in batch.
Step 3: Set the processing scope, specifying to process only cell text
Click "Next" to enter "Set Processing Options". In the screenshot, the "Processing Scope" area has Cell Text checked, while "Worksheet Sheet Name" and "Text on Shapes in Worksheet" are unchecked. For this example, what needs to be deleted are codes and identifiers in Excel cells, so processing only cell text is sufficient.
The advantage of this setting is a clearer processing target: the software will look for matches within the spreadsheet cell contents and will not modify worksheet names or shape texts. For workbooks containing graphics, comments, and descriptive text, defining the processing scope clearly can reduce the risk of incorrect processing.
In the "Cell Formula Types to Process" area, "No Limit" is selected in the screenshot; the "Processing Method for Cells Containing Formulas" is "Value after formula calculation"; and the "Cell Data Types to Process" has "Text" checked. These options indicate that this task mainly targets text-type cell content. It should be noted that many codes in Excel, while they appear numeric, are often stored as text to preserve leading zeros, like 030006. If you want to delete such codes, checking the text type is reasonable.
Step 4: Select "Fuzzy Text Search Using Formulas" and enter wildcard regular expressions
Continue viewing the "Set Keyword Options" area. In the screenshot, the "Search Method" is selected as Fuzzy Text Search Using Formulas, not "Exact Text Search". This step is crucial: if you were only deleting fixed text, you could use exact search; but this example requires deleting a category of codes, such as 030 followed by 3 digits, or 46 followed by 3 digits, thus requiring expressions for fuzzy matching.
In the "List of Keywords to Find", two rules are entered as shown in the screenshot:
- 030\d{3}: Means matching text starting with 030, followed by 3 digits, such as 030006, 030010, 030111.
- 46\d{3}: Means matching text starting with 46, followed by 3 digits, such as 46060.
The "List of Keywords after Replacement" on the right is empty, and the interface prompts "Leaving it blank means deletion". Therefore, the meaning of this setup is: any content matching the left-side expressions will be replaced with nothing, i.e., batch deleting these keywords.
If you need to delete content in other formats, you can modify the expressions following the same logic. For example, to delete all codes starting with AB followed by 4 digits, you can set a rule like AB\d{4}; to delete project codes with a fixed prefix, you can also write rules based on the actual format. It is recommended to test with a small number of files first before batch processing to confirm the expression matching results meet expectations.
Step 5: Set the save location and start processing
After completing the keyword rule setup, click Next at the bottom of the interface to enter "Set Save Location". This step is already displayed in the top process bar, indicating the software will require you to determine the output location before processing. It is recommended not to overwrite the original files directly, but to save to a new folder for easy comparison and verification after processing. Once you confirm the rules are correct, proceed to the "Start Processing" step to execute the batch task.
After processing is complete, open the output Excel files for verification. You should see that cell contents matching 030\d{3} and 46\d{3} have been cleared, while content not matching the rules is retained. The post-processing screenshot earlier shows this exact result.
Recommendations for Setting Wildcards and Regular Expressions
When batch deleting keywords in Excel, the accuracy of the expression directly determines the reliability of the processing result. Here are a few practical suggestions:
- Analyze data patterns first: For example, code length, starting characters, whether they include letters, or if there are fixed delimiters.
- Start with small-scale testing: First test the expression with 1 or 2 files to confirm the processing effect before batch processing all files.
- Pay attention to leading zeros: Codes like 030006 might lose leading zeros if treated as numbers by Excel; the codes shown in the screenshot retain leading zeros, and are usually better handled as text.
- An empty replacement list means deletion: In this example, the right-hand "List of Keywords after Replacement" was left empty, and the software prompt "Leaving it blank means deletion" means matching content will be cleared.
- Be cautious with overly broad rules: For example, using only \d+ might match all numbers, leading to massive erroneous deletions. You should try to add constraints like prefixes and length.
Common Questions and Precautions
1. Does this method delete entire rows?
No. From the effect shown in the screenshot, this operation deletes the matched cell text content, not entire rows or columns. After processing, columns like Hospital Name and CBSA Name are still retained, and the spreadsheet structure is not damaged.
2. Can multiple Excel files be processed simultaneously?
Yes. The screenshot shows 3 xlsx files imported at once, and the software will apply the same find-and-replace rules to all files in the list. For batch processing xlsx, xls reports, this is more efficient than opening Excel files one by one.
3. Why is the list of keywords after replacement left blank?
Because the goal this time is to delete keywords, not replace them with other text. The interface prompt "Leaving it blank means deletion" means that if nothing is filled in on the right, the matched keywords will be cleared.
4. What if I only want to delete fixed keywords and don't need expressions?
If the content to be deleted consists of completely fixed words or codes, you can choose "Exact Text Search" and enter the fixed content in the keyword list. Only when the content has a pattern but the specific values are not identical is it more suitable to use fuzzy formula search or similar regex rules.
5. Do I need to back up my files before processing?
Backup is recommended. Although the software provides a save location setting, when dealing with batch content deletion, it's best to output the results to a new folder and keep the original files for easy traceback and comparison.
Summary: Using Batch Processing Tools to Reduce Repetitive Excel Cleanup Work
When many Excel spreadsheets contain similar codes, identifiers, or keywords that need to be deleted, manual find-and-replace is not only inefficient but also prone to omissions. With the help of HeSoft Doc Batch Tool , you can use the "Find and Replace Keywords in Excel" function to add multiple xlsx files to a processing list at once, then use wildcard regular expressions to locate target content, and leave the replacement result blank to achieve batch deletion.
In this article's example, the two rules 030\d{3} and 46\d{3} were used to batch clean specified codes and area codes in Excel cells. For high-frequency office tasks like data masking, report distribution, code cleanup, and batch replacement, this method can significantly reduce repetitive operations and improve file processing efficiency. It is recommended to test rules with a small number of files before formal processing, and proceed with batch execution only after confirmation, which ensures both efficiency and safety.