, when sorting out Excel tables, many people will encounter the problem of fixed suffixes, abbreviation marks or repeated keywords in cells. Manual deletion one by one is not only slow, but also easy to miss. This article combines office software" HeSoft Doc Batch Tool "The actual operation interface demonstrates how to use wildcards/regular expressions to delete keywords in Excel in batches and complete unified processing of multiple files. You will see the effect comparison before and after processing, understand how to import xlsx, xls and other table files, how to set the cell text processing range, how to enter the search formula and leave the replacement content blank to realize deletion, thus efficiently cleaning data and reducing duplication of labor.
When processing Excel data, a common type of inefficient work is that some suffixes, abbreviations or marks appear repeatedly in a column of content, such as "WKB" and "BL" at the end of the commodity name, which need to be deleted uniformly. If you only rely on manual search and replacement, it is not only time-consuming in a single table, but also error-prone when facing multiple xlsx and xls workbooks. This article is to solve, isBatch delete keywords in Excel with wildcard regular expressionThis problem is combined with office software HeSoft Doc Batch Tool The actual interface, clearly explain the specific operation process.
Through this kind of batch processing office software, you can turn the work that originally needed to be repeated cell by cell and file by file into a configuration and batch execution, which is especially suitable for people who often sort out product lists, customer data, material names and export reports.
Applicable Scenarios
This method is suitable for you if you encounter the following situations:
- Excel cell text with uniform abbreviations at the end, need to be deleted in bulk.
- The same type of keywords are distributed in multiple worksheets and multiple Excel files.
- You need to delete by rule, not just a fixed complete word.
- Want to deal with xlsx, xls and other form files to reduce duplication of work.
From the example screenshot, the table inItem NameText such as "Long-grain rice, parboiled, WKB" "Round-grain rice, BL" "Wheat flower, WKB" is listed. The goal is not to delete the entire article, but to remove only the keyword tag at the end and the separator space in front of it, leaving the principal name.
Effect Preview
Before treatment
Before processing, Excel cells contain a variety of trailing tags, such:
- Long-grain rice, parboiled, WKB
- Round-grain rice, BL
- Wheat flour, WKB
These keywords do not appear to be exactly the same length, but they all appear at the end of the text, preceded by a comma and a space.
After treatment
After processing, the last keyword is uniformly deleted, and the result becomes:
- Long-grain rice, parboiled
- Round-grain rice
- Wheat flour
In other words, the software does not simply empty the entire content, but only deletes the matching part of the text according to the set wild or regular rules.
Operation steps
Step 1: Enter the Excel keyword search and replace function
As you can see from the screenshot, in HeSoft Doc Batch Tool Left Select"Excel Tools"After that, there are"Find and replace keywords in Excel". The purpose of this step is to enter a module that specializes in finding, replacing, and deleting Excel content.
Expected results: Once you enter the features page, you will see a multi-step interface for importing files, setting processing options, and starting processing.
Step 2: Add Excel files that need to be batch processed
After entering the function page, there is"Add File"and"Import files from a folder"button. Multiple xlsx files have been imported in the screenshot. The name, path, extension, creation time, modification time, and other information can be seen in the list.
The purpose of this step is to import the Excel file to be processed into the software at one time to facilitate the subsequent unified implementation of the rules.
If you only have a small number of files, you can click"Add File"; If you want to process multiple xls and xlsx files in one directory at a time, use it directly."Import files from a folder"More efficient.
Expected result: the file will be displayed in the record list after import, and click at the bottom after confirmation."The Next Step"Go to the settings page.
Step 3: Set the processing range to cell text
In the "Set Processing Options" page, the screenshot shows that it is checkedCell Text". This means that the object of this process is the text content in the Excel cell, not the text on the worksheet name or shape.
The purpose of this step is to ensure that the software only takes effect on the data areas that really need to be cleaned.
At the same time, from the screenshot can also see:
- The type of cell formula processed is selected"Only process cells that do not contain formulas";
- The data type of the cell to be processed is checked."Text".
This type of setting is useful because many Excel cells have both formula and text-only cells. The current case is to remove the keywords in the name, so it is safer to process only the text.
Expected result: The software will lock the processing range in the text cell, reducing the risk of miscorrection.
Step 4: Select the fuzzy search method and enter the wildcard/regular expression.
This is the most critical step in the whole operation. According to the screenshot, in the "Set Keyword Options" area, the search method is selected"Find Text Using Formula Blur"instead of "exactly find text".
on the left"List of keywords to find", the input is:
,\s [A-Z] $
The meaning of this expression can be understood in disassemble:
- ,: matches commas;
- \smatches one or more white space characters after a comma;
- [A-Z]: Matches one or more uppercase English letters, such as WKB, BL;
- $The qualified match position is at the end of the cell text.
Taken together, this rule matches:Tail keywords such as ", WKB" ", BL" at the end of the cell.
The purpose of this step is not to write only one word, but to let the software delete all keywords that conform to the pattern at once according to the rules, which is suitable for the situation where there are many different abbreviations in the data.
Expected result: The software looks for text fragments that match the rule at the end of each cell.
Step 5: Leave the replacement blank to achieve the "delete" effect
The right side of the screenshot is"List of keywords after replacement"Area, with a hint next to it"If it is not filled in, it means deletion". In other words, there is no need to fill in any replacement text here, just keep it empty.
The purpose of this step is to delete the "found content" directly instead of replacing it with other characters.
For the example in this article, the software will directly remove the ", WKB", "BL" and other contents matched at the end after leaving blank.
Expected result: The matched keyword disappears from the cell text, leaving only the preceding main content.
Step 6: Continue to the next step and perform batch processing
After completing the find rules and replace settings, click at the bottom of the page"The Next Step", enter the subsequent save location and start processing phase. Although the screenshot does not fully expand the final execution interface, it can be seen from the top process, followed"Set Save Location"and"Start processing"steps.
The purpose of this step is to confirm the output mode and formally execute the batch delete operation.
Expected results: The software batch processes the imported Excel files according to the set rules, and outputs the result files after deleting keywords.
Why can this expression delete the end keyword in batch
When many users search for "Excel wildcard delete text", "Excel regular expression replace" and "Excel batch delete keywords", the real difficulty is not which button to click, but how to write the rules.
The expression in the example in this article,\s [A-Z] $It fits the current scenario because it captures three characteristics:
- The keyword is preceded by a comma;
- Keywords are capital English abbreviations;
- The keyword appears at the end of the text.
Therefore, it will not arbitrarily delete the middle normal content, but more accurately locate the tail mark. It is especially practical for commodity name cleaning, material code suffix cleaning, classification label removal and so on.
Frequently Asked Questions or Precautions
1. Is this to delete fixed words or delete according to rules?
Judging from the screenshot settings, the one used here isFormula Fuzzy Find TextIn essence, it is deleted after matching according to rules, not limited to a fixed word. It is therefore more flexible than mere precise replacement.
2. Why check "cell text"?
Because this example is dealing with the text content in the worksheet. If your goal is to remove keywords from sheet names or text from graphical objects, you need to adjust the processing range to the actual scene. What is explicitly checked in the current screenshot isCell Text.
3. Why is it recommended to process only text cells?
If the table is mixed with numbers, dates, and formula results, rashly processing them all may affect the original data. The method in the screenshot is to check onlyTextdata type, and selectOnly process cells that do not contain formulas, more suitable for data cleaning scenarios.
4. What happens if you leave the replacement blank?
According to the interface prompt,If not filled in, delete. This is the core setting for batch deletion of Excel keywords.
5. Which Excel formats are suitable for processing?
As can be seen from the example of the interface file list, the current import is an xlsx file. In general, the Excel processing scenarios of this kind of office software are mainly aimed at common table formats such as xlsx and xls. If you want to process multiple workbooks in batches, it will be more efficient to import them according to the same rule set first.
Summary
If you want to finish efficientlyExcel Batch Delete Keywords,Excel wildcard delete textorExcel Regular Expression Replacement DeleteThis kind of work, the use of professional office software will be much more time-saving than manual modification. Like HeSoft Doc Batch Tool Such batch processing office software can string "import multiple Excel files-set text range-input matching rules-blank replacement for deletion-unified output" into a standard process, which is especially suitable for data cleaning tasks with high repeatability.
If there is a similar "abbreviation mark at the end of the name body" in your form, it is recommended to try it directly according to the steps in this article: import xls or xlsx file first, and then use similar,\s [A-Z] $The rules test a batch of sample files. After confirming that the results are in line with expectations, batch processing of formal documents can significantly reduce duplication of labor and improve the efficiency of Excel data sorting.