When faced with batches of numbers, codes, or sensitive keywords across multiple Excel workbooks, manual deletion makes it difficult to ensure both efficiency and accuracy. This article, using screenshots from HeSoft Doc Batch Tool , explains how to import multiple xlsx files, select the cell text processing range in the "Find and Replace Keywords in Excel" feature, enable formula-based fuzzy text search, and enter rules such as 030\d{3} and 46\d{3} to batch replace matched content with empty values, enabling batch clearing of keywords across multiple Excel files.
Many office workers encounter similar issues when organizing Excel reports: there are numbers or codes in the table that need to be deleted, but these entries aren't a single identical word; rather, they are a set of values with the same format. For example, a batch of 6-digit codes starting with 030 in a hospital ID column, or a batch of 5-digit codes starting with 46 in a region code column. Even more troublesome, this content is distributed across multiple Excel files, multiple worksheets, or a large number of data rows. If you open files one by one to search and then manually delete them, it not only consumes time but may also lead to omissions due to incomplete filtering ranges or incorrect replacement conditions.
For such "multi-file, repetitive, rule-based" Excel content cleanup tasks, it is more suitable to use batch processing office software. This article takes HeSoft Doc Batch Tool as an example to illustrate how to use its Find and Replace Keywords in Excel function to batch clear matching content using wildcard regular expressions. In the example, we will delete two types of content: numbers matching 030\d{3}, and codes matching 46\d{3}. Since the replacement content is left blank, the software will delete the matched items, meaning it clears the keywords from the corresponding cells.
This tutorial is suitable for users who need to batch process Excel spreadsheet files like xlsx, xls, etc., and is especially fitting for scenarios such as data masking, report cleaning, number cleanup, and batch keyword deletion. After reading, you can set different expressions based on your own data patterns to achieve similar automated processing.
Applicable Scenarios: Why Use Regex Rules to Batch Delete Excel Content
Excel's built-in find-and-replace feature is suitable for handling small amounts of fixed text within a single workbook, but manual operation becomes inefficient in the following scenarios:
- Large number of files: For example, dozens of monthly reports, project tables, and customer lists in a folder need the same deletion rules applied.
- Keywords are not fixed values: For instance, deleting numbers from 030001 to 030999—listing keywords individually is unrealistic.
- Need to preserve other content: Only delete matching numbers or codes, not entire rows or columns, and do not affect fields like names, regions, or descriptions in the table.
- Processing requirements are reusable: The same set of rules can be applied to multiple files, suitable for regular report processing.
- Accuracy required: Rules are executed by software after configuration, reducing omissions and erroneous deletions caused by manual operation.
For example, in the Excel table shown in this article's screenshots, the Hospital CCN column and CBSA column contain numbers and codes that need to be cleaned up. They are not exactly the same word but have distinct formats: 030 followed by 3 digits, and 46 followed by 3 digits. By describing the pattern with an expression, the software can automatically identify such content.
Result Preview: From Excel with Numbers to Batch Cleared Results
Before Processing: Numbers and Codes Distributed Across Different Columns
In the screenshot before processing, the Excel table contains fields such as Hospital Name, Hospital CCN, CBSA Code, and CBSA Name. Red arrows point to the two types of content to be processed: numbers starting with 030 in the Hospital CCN column, and the code 46060 in the CBSA column. This content appears consecutively in multiple rows; deleting them row by row manually would involve a huge workload.
It is important to note that the goal of this example is not to delete the characters "030" themselves, nor all numbers, but numbers that match the complete rule. For instance, 030\d{3} means there must be 3 more digits after 030, which prevents the rule from being too broad and accidentally deleting other unrelated content.
After Processing: Cells Matching the Rules Are Cleared
The screenshot after processing shows that the cells originally containing numbers starting with 030 have become blank, and the codes matching the 46-starting rule in the CBSA column have also been cleared. Meanwhile, other fields like hospital names, city names, and state information still exist, indicating that the software performed "find matching content and replace with empty," without destroying the table structure.
This processing effect is very suitable for data cleaning before sharing reports: sensitive numbers are deleted, necessary business information is retained, and the file can still be used for reading, filtering, and statistical analysis.
Operation Steps: Batch Import Excel and Set Deletion Rules
The complete workflow is shown below according to the software screenshots. The entire process requires no macro writing or opening Excel files individually; the core is to import the files for processing all at once, and then set unified find and replace rules.
Step 1: Open the Find and Replace Function in the Excel Tool
After launching HeSoft Doc Batch Tool , the left side shows functional category navigation, including Word Tools, Excel Tools, PowerPoint Tools, PDF Tools, etc. Since we need to process Excel tables this time, first click Excel Tools on the left. Then select "Find and Replace Keywords in Excel" from the function cards on the right.
The purpose of this step is to enter the batch find-and-replace module for Excel content. In the screenshot, this function is described as "Batch find and replace keywords in Excel file content," which perfectly matches the goal of this article: "batch delete keywords from many Excel spreadsheet files." Deleting keywords can be understood as a special kind of replacement: replacing the found content with nothing.
Step 2: Add Multiple xlsx Files to Process
After entering the function, the progress flow is displayed at the top: Select records to process, Set processing options, Set save location, Start processing. First, we are at Step 1 "Select records to process." In the upper right corner, you can use Add File or Import Files from Folder to add Excel files.
In the screenshot, three files have been added: product_list.xlsx, team-participant-list.xlsx, test.xlsx. The list also shows the file path, extension, creation time, and modification time, making it easy to confirm if the correct files have been imported. If a file does not need processing, it can be removed via the delete icon in the action column; if files were added incorrectly, you can also use "Clear" to reselect.
For batch office tasks, it is recommended to place the same batch of Excel files to be processed into one folder, and then use "Import Files from Folder." This can reduce the time spent selecting files one by one and also makes it easier to manage pre- and post-processing files.
Step 3: Select Cell Text as the Processing Scope
After importing the files, click Next to enter "Set processing options." In the screenshot, under "Processing Scope," Cell Text is checked. This means the software will search for keywords within Excel cell contents. Since this article aims to delete numbers and codes from the table, not worksheet names or shape text, checking only Cell Text is the safer choice.
The same page also contains several options related to cell processing. In the screenshot, "Cell formula types to process" is set to "No restriction"; "How to process cells containing formulas" is set to "Value after formula calculation"; and "Cell data types to process" has "Text" checked. These settings allow the software to focus on text-based cell content. Many number-like data entries, to preserve leading zeros, are stored as text format, for example, 030006. If Text is not included in the processing scope, such numbers may not be matched.
If the keywords in your actual table are stored as number types, you should also check the interface options to see if the corresponding data type is selected. However, based on the example in this article, the target content is primarily text-formatted numbers and codes, so the settings in the screenshot meet the requirements.
Step 4: Enable Fuzzy Formula Search and Fill in the Expression Keyword List
In the "Set Keyword Options" area, the search methods are "Exact text search" and "Fuzzy search using formulas." In the screenshot, Fuzzy search using formulas is selected. When the content you want to delete is patterned but not identical, this method should be chosen.
In this example, two lines are filled into the "Keyword list to search":
- 030\d{3}
- 46\d{3}
This can be understood as: \d represents a digit, and {3} represents exactly 3 consecutive digits. Therefore, 030\d{3} will match content with 030 followed by 3 digits; 46\d{3} will match content with 46 followed by 3 digits. Compared to manually entering multiple numbers like 030006, 030010, 030011, expressions are more concise and better suited to covering large amounts of data with the same format.
On the right is the "Keyword list after replacement." In the screenshot, this list is empty, with a hint saying "Leave blank to delete." Therefore, this processing does not replace the numbers with other characters, but directly clears the matched content. This design is very convenient for batch keyword deletion: simply write the search rules on the left and keep the right side empty.
Step 5: Go to Save Location Settings and Execute Batch Processing
After setting the keywords, click Next at the bottom. Following the top progress flow, the next stage is "Set save location," and the final stage is "Start processing." Although the screenshot does not show the save location page in detail, it's clear from the wizard workflow that the software will let users set the output location before starting processing.
It is recommended, when batch deleting Excel content, to save the processed results to a new folder rather than overwriting the original files. This has two benefits: first, you can always go back to the original files for verification; second, if the expression settings are not accurate enough, you can adjust the rules and process again. After confirming the save location, proceed to "Start processing" and wait for the software to complete the batch find-and-replace.
After processing finishes, open the output file to check the key columns. Content matching the expressions should have been cleared, while other non-matching data should remain unchanged. If the results meet expectations, the same method can be applied to more Excel files.
Expression Writing Tips: How to Avoid Erroneous Deletions and Omissions
The advantage of regex rules is their flexibility, but they need to be written accurately. The following suggestions can help you reduce risks when batch deleting Excel keywords:
- The more specific the rule, the safer: For example, to delete a 6-digit number starting with 030, writing 030\d{3} is more accurate than just writing 030.
- Do not use overly broad digit rules: If you write \d{5}, it might match all 5-digit numbers, not just the target codes.
- Judge based on actual column content: If a column contains both numbers and other digits, try to include a fixed prefix to limit the matching scope.
- Test with a file first: Copy one Excel file for testing, confirm the deletion results are correct, and then batch import all files.
- Keep the original files: Batch deletion is irreversible content modification; it is recommended to always keep the original versions.
If your keywords are fixed text, such as "void," "delete," "test data," you might not need to use expressions; you can use exact text search. If keywords are dynamic numbers, sequential IDs, or code segments, using fuzzy formula search is more appropriate.
Frequently Asked Questions: What to Know Before Batch Clearing Excel Matched Content
1. Will batch keyword deletion affect formulas?
The screenshot shows options like "Cell formula types to process" and "How to process cells containing formulas." The example in this article selects "No restriction" and "Value after formula calculation." If your workbook contains many formulas, it is recommended to first verify the results with a test file to confirm they meet your business requirements.
2. Why do cells become empty instead of entire columns being deleted after processing?
Because this process uses the Find and Replace function, and the replacement keyword list is empty, it means the matched text is replaced with blank. It does not automatically delete entire rows or columns, so the table structure is preserved.
3. Can it process docx, doc, or PDF files?
This article discusses Excel file content processing, applicable to spreadsheet scenarios like xlsx, xls. The software interface also has categories for Word Tools, PDF Tools, etc., but different file types require entering the corresponding tool module. When processing Word docx or doc documents, you should select Word-related functions; for PDFs, choose PDF Tools.
4. What if the expression doesn't take effect?
You can check three things: first, whether "Fuzzy search using formulas" is selected; second, whether the keyword rule matches the actual content format; third, whether the target cell's data type is checked. For instance, numbers with leading zeros are often text type—ensure Text is included in the processing scope.
5. How do multiple expressions correspond to replacement results?
In this example, the replacement keyword list on the right is empty, so all matched items are deleted. If you are not deleting but replacing with other content, you need to fill in the replacement keywords according to the software interface requirements. This article only discusses the batch deletion scenario where replacement is set to empty.
Summary: Transforming Excel Keyword Cleanup from Manual Work to a Batch Process
Batch deleting keywords from Excel files is essentially a typical repetitive office task. As long as the target content has a pattern, there is no need to manually process file by file, cell by cell. Using HeSoft Doc Batch Tool , you can first import multiple Excel files, then set the processing scope, search method, and expression rules in the "Find and Replace Keywords in Excel" function, and finally leave the replacement content empty to achieve batch clearing of matched content.
The example in this article used 030\d{3} and 46\d{3} to delete specified numbers and codes from an Excel table, preserving other business data and the table structure after processing. For users who frequently organize reports, clean up numbers, or perform data masking, this method can significantly reduce repetitive labor and improve batch file processing efficiency. It is recommended to first verify the expressions with a small sample, and then execute batch processing on the complete folder to balance efficiency and accuracy.