When processing data and sorting out tables at work, Excel worksheets may accumulate a large number of texts or numbers with similar names and repeated structures, such as product numbers, customer names, address information, etc., and there are a large number of similar contents that are exactly the same. Such data may reveal important private information. If it is not cleaned up and deleted, it will not only affect the data leakage, but also interfere with the accuracy of subsequent statistical analysis. When facing hundreds of redundant texts that are highly similar but not identical, it is a waste of time to manually find and delete them. Secondly, it is also possible to delete key data in the table, making the information incomplete. Is there a way to quickly identify these texts and numbers with similar structures and automatically delete them in batches?
This article explains how to use fuzzy matching techniques to quickly locate unnecessary data with similar structures in Excel for batch deletion to create a cleaner and more professional spreadsheet. Let's take a look at the specific operations together!
When to batch delete Excel worksheet structure similar text, numbers?
there are a large number of comments, numbers or dates with the same structure in Excel files received from others. These information are useless and will affect the statistics and sorting of data. Manual deletion takes a long time. You can use fuzzy search to delete similar words and numbers in batches.
Clean up template duplicate data
when you want to import Excel numbers into the system or software, the format of the table needs to be unified, but there may be repeated serial numbers, identifiers or numbers in the original data. By deleting these similar structures in batches, you can avoid import errors and let the system automatically identify clean original content.
Remove duplicate template content
when some departments of the enterprise do project summary or weekly reports, they will copy multiple copies of the same template, but each form has similar explanatory text, examples or figures. If they are not deleted, a large number of forms will only be at sixes and sevens when viewed. We can use the batch delete function to remove these contents with the same structure, keep the real data, make the files cleaner, and make it easier to submit, print and merge later.
Using fuzzy matching to batch delete Excel keywords effect preview
before treatment:
after treatment:
Use fuzzy search batch delete Xls, Xlsx structure similar text and number of operating steps
1. Open 【 HeSoft Doc Batch Tool ], select [Find and Replace Keywords in Excel] in [Excel Tools]].
2. Select a method in [Add File] or [Import File from Folder] to add Excel file with similar text structure to be deleted, or drag the file directly to the bottom to import, and finally click Next.
3. Enter the Excel option setting interface, check [Cell Text], and check it according to your specific situation.
4. Then see the setting keyword option interface, select [Use Formula Fuzzy Search Text], enter the corresponding regular expression formula below the searched keyword list, and leave blank below the replaced keyword list. Finally, click Next to enter the save page, click Browse, and select the save location of the new file.
5. After waiting for the processing to be completed, click the red path to open the folder to view the Excel file with successfully deleted keywords,
