If multiple Excel files contain a large number of codes starting with 030, 46, or other fixed-format keywords, you can use regular expressions to delete them in batches. This article uses a hospital list table as an example to explain how to use HeSoft Doc Batch Tool to select the Excel Find and Replace function, import multiple xlsx files, set the processing scope to cell text, and use the rules 030\d{3} and 46\d{3} to match content, leaving the replacement result blank to clear them in batches and reduce repetitive work.
When handling business lists, statistical reports, or Excel files exported from systems, you often encounter a need: some numbers or codes in the table no longer need to be displayed and must be uniformly deleted. For example, numbers like “030006, 030010, 030064” and area codes like “46060” in certain records. These are distributed across multiple xlsx files, and opening each file to delete them manually would be very time-consuming.
This article uses the Excel hospital list from the screenshot as an example to demonstrate how to use HeSoft Doc Batch Tool to batch delete numbers starting with 030 and codes starting with 46 from Excel tables. The key point of this method is using wildcard regular expressions for fuzzy matching and leaving the replacement content empty, thereby achieving batch keyword deletion. After reading, you can apply the same approach to other Excel data cleanup tasks, such as product codes, customer IDs, order numbers, and area codes.
Applicable Scenario: Batch Deleting Patterned Excel Keywords
If the content you want to delete is completely fixed, such as deleting the word “Test Data” from all files, a simple Find and Replace will suffice. However, in many office scenarios, the content to be deleted is often a string that "has a pattern but is not exactly the same." For example:
- 6-digit numbers starting with 030: 030006, 030010, 030111.
- 5-digit codes starting with 46: 46060, 46123, 46999.
- Product codes starting with a specific letter or number prefix.
- Fixed-format employee IDs, department codes, project numbers.
- Data fields that need to be masked.
This type of content is suitable for describing rules with regular expressions. For instance, 030\d{3} means "030 followed by 3 digits," and 46\d{3} means "46 followed by 3 digits." The advantage of using rule-based searching is that you don't need to list every possible number in a keyword list one by one.
HeSoft Doc Batch Tool is software designed for batch processing of office files. Its advantage lies in the ability to import multiple Excel files at once, delegating the repetitive find, replace, and delete actions to the tool.
Preview of Results: Comparison Before and After Processing
Before Processing: Numbers to be cleaned up exist in Excel
The "before" screenshot shows an Excel table with headers including Hospital CCN, Hospital Name, CBSA, CBSA Name, and other fields. In the area indicated by the red arrow, column B contains multiple numbers starting with 030, and column D contains the code 46060. This content is the target for deletion this time.
It is important to note that the target content is located only in specific cells; hospital names, city names, and other numbers in the table still need to be retained. Therefore, this operation is not about deleting entire columns or entire rows, but about deleting cell text that matches the rules.
After Processing: Target numbers cleared, other content preserved
After the processing is complete, the content that previously matched 030\d{3} and 46\d{3} has been cleared. In the screenshot, you can see that the corresponding positions for the 030 series numbers in column B are empty, and the corresponding code starting with 46 in column D is also empty, while other non-matching content is retained.
This is the result of "Regex Find + Replace with Empty." It is suitable for batch cleaning multiple files without destroying the overall structure of the Excel workbook.
Operation Steps: From Selecting Function to Setting Regex Rules
Step 1: Find the Find and Replace entry in the Excel tool
Open HeSoft Doc Batch Tool and go to the "Excel Tools" category on the left. On the right side, you will see multiple Excel batch processing feature cards. The one used this time is "Find and Replace keywords in Excel," whose function description is batch find and replace keywords in Excel file content.
After selecting this function, you can enter the specialized batch find and replace process. For situations with multiple xlsx files to process, this step is equivalent to consolidating manual operations originally scattered across Excel into a single batch processing task.
Step 2: Add files or import from a folder
After entering the function page, the first step is "Select records to process." Above the interface, there are options like "Add File," "Import Files from Folder," "Clear," and "More." You can choose the addition method based on the number of files: use "Add File" when there are few files, and "Import Files from Folder" when there are many files concentrated in one directory.
In the screenshot, the pending list contains 3 xlsx files: product_list.xlsx, team-participant-list.xlsx, and test.xlsx. The list also shows the file path, extension, creation time, and modification time for easy verification. After confirming they are correct, click "Next."
The expected result of this step is: All Excel files from which keywords need to be batch deleted are added to the task list. If files are added by mistake, they can be removed before proceeding to the next step to avoid unnecessary modifications.
Step 3: Set the processing scope to Cell Text
In the second step, "Set processing options," you first see "Set Excel Options." In the processing scope, there are options like "Cell Text," "Worksheet Sheet Name," and "Text on Shapes in Worksheet." In the screenshot, only "Cell Text" is checked.
Because the goal of this example is to delete numbers and codes from the data area of the Excel table, processing only the cell text is sufficient. This avoids accidentally modifying sheet names or text within graphical objects.
Step 4: Confirm formula and data type settings
On the same page, you can also set cell formula type and data type. In the screenshot, "Cell Formula Type to Process" is set to "No Limit," "Processing Method for Cells Containing Formulas" is set to "Calculated Value of Formula," and "Cell Data Type to Process" is set to "Text."
These configurations are suitable for processing data that looks like numbers but is actually stored as text. Many codes with leading zeros must be saved in text format, for example, 030006. If treated as a regular number, the leading zero might be lost. Therefore, when cleaning such fields, choosing the text type better matches the actual data characteristics.
Step 5: Choose to use formula for fuzzy text search
Continue scrolling down to "Set Keyword Options." In the search method, the screenshot shows "Use Formula for Fuzzy Text Search" selected, rather than "Exact Text Search." This is because we need to delete a class of strings that match a rule, not a single fixed string.
Enter into the "Keyword List to Find":
- 030\d{3}
- 46\d{3}
Each line here represents a search rule. The first line will match 6-digit numbers starting with 030, and the second will match 5-digit codes starting with 46.
Step 6: Leave the replacement content empty to achieve deletion
The "Replacement Keyword List" on the right is empty, and a prompt on the interface says "Leaving blank means deletion." Therefore, this action is not to replace the numbers with new ones, but to directly clear the matched content.
If your requirement is to replace them with uniform text, you can also fill in the corresponding content in the replacement list; but if the goal is to batch delete keywords, keep it empty. After finishing the settings, click "Next," continue to set the save location, and finally enter "Start Processing."
Common Questions and Precautions
1. Why not use exact text search?
Exact search is suitable for deleting fixed words, but in this example, the target content has multiple different values. Using exact search would require listing every single number, which is labor-intensive and prone to omissions. A regular expression can cover a class of numbers with a single rule.
2. Will the regular expression match partial content within a cell?
According to the functional logic of "Find and Replace Keywords," the rules are used to search for matching content within the text. To avoid accidental deletion, it's recommended to write the rules more specifically, such as fixing the prefix and length, rather than writing overly broad digit-matching rules.
3. What if the numbers are in numeric cells?
The screenshot shows "Text" was chosen for the data type. If the numbers in your file are actually stored as numbers, dates, times, or other types, you need to check the corresponding options based on the data type options provided in the interface. Before processing, you can check the cell format or use a test file to verify the matching effect.
4. Will batch processing overwrite the original files?
The screenshot shows the process includes "Set Save Location." For safety, it is recommended to save the processed results to a new directory and not directly overwrite the only original copy. This way, even if the rules are not suitable, you can go back to the original files and reprocess them.
5. Can it process Excel files in an entire folder?
The operation interface provides a "Import Files from Folder" button, suitable for adding Excel files from the same directory to the task list. For scenarios involving batch processing of numerous xlsx files, this is more convenient than adding them one by one.
Summary: Using Regex Replace to Empty for More Time-Efficient Excel Batch Cleanup
The key to batch deleting numbers starting with 030 and codes starting with 46 in an Excel table lies in two points: first, selecting the correct processing scope, dealing only with cell text; second, writing accurate regular expressions, such as 030\d{3} and 46\d{3}. In HeSoft Doc Batch Tool , fill these rules into the find list and leave the replace list empty to achieve batch deletion.
This method turns repetitive manual find, delete, and save actions into an automated workflow, making it particularly suitable for processing multiple Excel, xlsx, and xls report files. It is recommended to first copy a sample file to test the effect, confirm the matching rules are accurate, and then import the complete folder for batch processing, ensuring a safer and more efficient office data cleanup.