When the same type of numbers, codes, or sensitive keywords exist across multiple Excel sheets, opening each file individually to search and delete them is not only time-consuming but also prone to omissions. This article uses batch deletion of numbers starting with 030 and codes starting with 46 in Excel files as an example, introducing how to use the "Find and Replace Keywords in Excel" feature in HeSoft Doc Batch Tool to locate and clear matching content in multiple xlsx workbooks at once using wildcard regular expressions, suitable for office scenarios such as data cleaning, report desensitization, and number organization.
In daily office work, many people encounter similar problems: a batch of Excel tables contains a large number of serial numbers, area codes, project codes, or keywords in a certain fixed format that need to be uniformly deleted or cleared. If you only have one xlsx file, you can slowly process it using Excel's built-in Find and Replace; but if there are dozens or hundreds of files, opening, searching, deleting, and saving them one by one not only involves a lot of repetitive work but also makes it very easy to miss something. This is especially true when the content to be deleted is not a fixed word, but a patterned number like "030006, 030010, 030111"—ordinary exact searches are insufficient then.
The problem this article aims to solve is: using wildcards and regular expressions (regex) to batch delete keywords matching certain rules from multiple Excel files. In the example, we need to delete two types of content from multiple Excel spreadsheet files: one is a number starting with 030 followed by 3 digits, such as 030006; the other is a code starting with 46 followed by 3 digits, such as 46060. Using HeSoft Doc Batch Tool , you can configure these rules at once, letting the office software automatically batch find and replace with empty, thereby completing the batch deletion.
Applicable Scenarios: Which Excel content is suitable for batch deletion using regex
Regular expressions are suitable for handling content that has a "fixed format but not completely identical specific values." For example, you don't just want to delete one specific "030006," but all 6-digit codes starting with "030." In this case, you could write a rule like 030\d{3}, where \d represents a digit and {3} means 3 consecutive digits.
Common applicable scenarios include:
- Batch deleting serial numbers, transaction numbers, partial ID numbers, area codes, hospital codes, product codes, etc., from Excel.
- Performing data masking on multiple xlsx and xls sheets by clearing sensitive fields that match specific rules.
- Batch cleaning reports exported from a system by deleting unnecessary code columns or text segments.
- Uniformly processing Excel files submitted by multiple departments to avoid manual modification one by one.
- Deleting only the matching keywords while preserving the table structure, row/column positions, and other content.
The core of this type of requirement is not "editing one cell," but "batch processing many files." The value of HeSoft Doc Batch Tool as an office software lies in automating repetitive search, replace, and save actions, saving manual operation time.
Effect Preview: Changes in Excel Content Before and After Processing
Before Processing: Tables contain numbers and codes matching the rules
In the Excel table before processing, you can see multiple 6-digit codes starting with 030 in the "Hospital CCN" column, such as 030006, 030010, 030011, 030064, etc.; similarly, there are multiple 5-digit codes starting with 46 in the "CBSA" column, such as 46060. The red arrows in the screenshot mark the areas needing processing; these are digitally formatted strings that are not identical fixed text.
If deleted manually, one would first need to filter or find these codes, then clear them one by one. This might be barely manageable for a single file, but when a directory contains multiple Excel files, manual processing is extremely inefficient.
After Processing: Content matching the regex rules has been cleared
After processing is complete, the content matching the rules has been deleted. As shown in the image below, the positions of the codes starting with 030 in the original column B are cleared, and the codes matching the rule starting with 46 in column D are also cleared. Cells whose content does not match the rules remain, such as hospital names, city names, and other codes are not mistakenly deleted.
This shows that by batch replacing with empty strings using regular expressions, you can achieve "rule-based keyword deletion," rather than simply deleting entire rows, columns, or all numbers.
Operation Steps: Using office software to batch delete Excel keywords
Step 1: Enter the Excel Tools, select the Find and Replace function
After opening HeSoft Doc Batch Tool , select "Excel Tools" in the function category on the left. The main interface lists several Excel-related batch processing functions, such as format conversion, image export, password protection removal, etc. The function to use this time is the first one: "Find and Replace Keywords in Excel".
The purpose of this step is to enter the functional module for batch finding and replacing Excel content. Unlike Excel's built-in Find and Replace, this is oriented towards multiple Excel files, allowing you to add multiple xlsx spreadsheets at once for unified processing.
Step 2: Add the Excel files to be processed
After entering the "Find and Replace Keywords in Excel" interface, you first arrive at the "Select records to process" step. The top of the interface provides buttons like "Add Files," "Import Files from Folder," and "Clear." You can add individual Excel files or import a batch from a folder.
The screenshot shows that 3 xlsx files have been added, including product_list.xlsx, team-participant-list.xlsx, and test.xlsx. The file list displays information like name, path, extension, creation time, and modification time. After confirming the files are correct, click "Next".
The expected outcome of this step is that all Excel files needing keyword deletion are in the pending processing list. It is recommended to confirm the file paths before formal processing to avoid adding spreadsheets that should not be modified to the task.
Step 3: Set the processing scope, only process cell text
On the "Set Processing Options" page, you need to first determine the processing scope. In the screenshot, only "Cell Text" is checked, while "Names of Worksheet Sheets" and "Text on Shapes in Worksheets" are not. This indicates this operation only targets content within Excel cells, and does not modify worksheet names or process text in shapes or text boxes.
For batch deletion of numbers, codes, or keywords in table data, it's generally recommended to select "Cell Text". This reduces the risk of accidentally modifying table names, graphic text, etc.
Step 4: Set cell type and formula processing method
In the same page, you can further set the types of cell formulas to process, how to handle cells containing formulas, and data types. As seen in the screenshot, "Type of cell formulas to process" is set to "No restriction"; "Processing method for cells containing formulas" is set to "Value calculated from formula"; and "Cell data types to process" has "Text" checked.
The role of these options is to control the search scope and data reading method. For instance, some Excel cells look like numbers but might actually be in text format; some cells might contain formulas whose displayed results are what we see. According to the configuration in the screenshot, the software will match text content based on the displayed values, which is more suitable for processing numbers and codes in exported reports.
Step 5: Choose the fuzzy search method and input the regular expression
In the "Set Keyword Options" area, select "Use formula for fuzzy text search" as the search method. "Fuzzy search using formulas" here can be understood as matching using wildcards or regex rules, suitable for finding text with a common pattern.
In the example, two rules are entered in the "List of keywords to find":
- 030\d{3}: Matches content starting with 030 followed by 3 digits, e.g., 030006, 030010, 030111.
- 46\d{3}: Matches content starting with 46 followed by 3 digits, e.g., 46060.
On the right is the "List of keywords to replace with", which is left blank in the screenshot, and the interface prompts "Leaving blank means delete". Therefore, the meaning of this task is: find the content matched by the rules on the left and replace it with empty, i.e., batch delete.
Step 6: Set the save location and start processing
According to the process bar at the top of the interface, after completing the processing options setup, you need to proceed to "Set Save Location" and "Start Processing". The save location is typically used to decide where the processed files will be output; it's recommended to choose a new folder to save the results for easy comparison with the original files. Finally, enter the start processing step to let the software automatically batch perform find, replace, and save.
Although the screenshot does not show the specific button details for the latter two pages, the process bar clearly indicates that this function uses a four-step wizard: Select Records, Set Processing Options, Set Save Location, Start Processing. Complete them in order.
FAQ and Precautions
1. What does \d{3} in the regular expression mean?
\d represents any digit, and {3} means repeat 3 times. Therefore, 030\d{3} means "030 plus 3 digits," matching any 6-digit string from 030000 to 030999. Similarly, 46\d{3} can match any 5-digit string from 46000 to 46999.
2. Does leaving the replacement content blank really mean delete?
As can be seen from the interface prompt, next to the "List of keywords to replace with" it's noted "Leaving blank means delete". Therefore, if your goal is to batch clear the matched content, you only need to fill in the find rules and leave the replace results blank.
3. Will it delete entire rows or columns?
The operation in this example is to find and replace cell text, not to delete rows or columns. After processing, you can see that the matched content in the original positions is cleared, while hospital names, city names, etc., in other columns remain, and the table structure is not damaged.
4. Is a backup needed before processing?
It is recommended to keep the original Excel files or output the processed files to a new directory when "Setting the save location." Batch processing is very efficient, but if a regex rule is written too broadly, it may delete more content than expected. It's safer to test with a small number of files first before batch processing all files.
5. Can both xls and xlsx files be processed using this approach?
The example file extension in the screenshot is xlsx. For xls, xlsm, and other Excel-related formats, whether they can be processed depends on the software's actual support and import results. If the files can be successfully added to the list, you can continue setting search and replace rules using the same approach.
Summary: Using rules instead of manual searching cleans up Excel in batches more efficiently
When the number of Excel files is large and the content to be deleted has a fixed pattern, wildcard-based regular expressions are a very efficient processing method. Using the "Find and Replace Keywords in Excel" function of HeSoft Doc Batch Tool , you can add multiple xlsx files to the task list, set the scope to cell text, then use rules like 030\d{3} and 46\d{3} to batch locate content and delete it by replacing with empty strings.
Compared to opening files one by one and searching cell by cell, this method is more suitable for report cleaning, data masking, and batch organizing in office scenarios. It is recommended that you first prepare a test file to verify the regex rules, and after confirming the effect is correct, perform batch processing on the entire folder.