In inventory sheets, asset ledgers, and product lists, old categories, locations, or statuses often exist and need to be uniformly replaced across multiple Excel files. This article uses examples to illustrate how to use HeSoft Doc Batch Tool to batch find specified text in Excel, replace old keywords with new keywords, and enable highlighting of the replaced content. After processing, all modified cells will be marked with a prominent color, facilitating data cleaning, review, and archiving.
Inventory tables and ledger files share a common characteristic: the format is relatively fixed, but there are many files. For example, one inventory table per warehouse, one asset ledger per project, and one inventory record per cycle. When business names change, the old text in the tables needs to be updated in batches. Common requirements include: replacing old classifications with new ones, replacing old warehouse names with new warehouse names, and replacing old status names with a unified standard.
Using only Excel's single-file find and replace function, users must repeatedly open each file, execute the find and replace, and save and close. The more files there are, the more likely issues such as missed processing, missed saves, and unclear replacement locations become. This article introduces a method more suitable for batch office processing: using " HeSoft Doc Batch Tool " to batch replace specified text in multiple Excel tables and highlight the replaced content, helping users quickly complete data cleanup.
Applicable Scenarios: Batch Text Cleanup for Inventory Tables, Ledgers, and Lists
Batch replacement of specified text and highlighting in Excel is suitable for all scenarios with "clear rules, many files, and a need for review." Taking inventory management as an example, the product classification is adjusted from "Stationery" to "Office supplies," and the warehouse location is adjusted from "Warehouse C" to "Warehouse B." This content might be scattered across different xlsx files or appear in different cells within the same workbook.
The same method can also be used for asset ledger cleanup. For instance, unifying old department names into new ones, standardizing old office locations into new ones, or adjusting certain status values to standard wording. For administrative, HR, finance, procurement, warehousing, and operations personnel, this type of batch processing can significantly reduce repetitive work.
Compared to manual replacement, the advantage of batch processing tools is not just speed but also a clearer process. Users first import files, then set replacement rules, and finally output results uniformly. With highlighting enabled, the replacement results are presented in eye-catching colors, facilitating subsequent review.
Effect Preview: More Intuitive Before-and-After Comparison
Before Processing: Old Text Requires Item-by-Item Cleanup
In the Excel table before processing, the Product Inventory List file contains fields such as Product ID, Product Name, Category, Status, and Location. The cells marked with red boxes in the screenshot are the old text needing cleanup, including multiple instances of "Stationery" and some of "Warehouse C." Although this content may seem uncomplicated, it is scattered across multiple rows of data.
If there is only one copy of the inventory table, manual operation is feasible; if a directory contains many copied files or versions from different dates, manual cleanup becomes inefficient. More importantly, it is difficult to prove which content has been successfully replaced after manual processing. Therefore, this article opts for the method of batch find-and-replace with highlighting.
After Processing: New Text Replaced and Highlighted
In the processed screenshot, the original "Stationery" has been replaced with "Office supplies," and the original "Warehouse C" has been replaced with "Warehouse B." The replaced cells have a yellow background, indicating that the software not only completed the text replacement but also highlighted the replacement results.
This effect is highly suitable for review after data cleanup. For example, after processing a batch of inventory tables, users can open any result file and directly check the yellow highlighted areas to confirm whether the replaced classifications and warehouses are correct. Without highlighting, reviewers might need to perform further filtering or searching, significantly reducing efficiency.
Operation Steps: From File Import to Batch Replacement Completion
Step 1: Open the Excel Tools Category
In the main interface of HeSoft Doc Batch Tool , the function navigation area is on the left. According to the screenshot, you need to select "Excel Tools" first. After selection, the right side will display a list of batch functions related to Excel files. The function needed this time is the first one: "Find and Replace Keywords in Excel."
The purpose of this function is to batch find and replace keywords in Excel file content. For inventory table and ledger cleanup, this precisely corresponds to the need to "find old text across many Excel files and uniformly change it to new text." Clicking this function leads to the specific processing page.
Step 2: Add the Excel Files to be Processed to the Task
On the task page, you can see "Add Files" and "Import Files from Folder" at the top. If the number of target files is small, you can add them individually; if all inventory tables or ledgers are concentrated in one folder, "Import Files from Folder" is more suitable for batch office scenarios.
After importing, the files will be displayed in the list. The screenshot lists multiple xlsx files related to the Product Inventory List, showing information such as path, extension, creation time, and modification time. Users can confirm the import scope through the list and delete incorrectly added files.
Careful checking is recommended at this step. The nature of batch replacement is processing multiple files at once; if unrelated files are mistakenly added to the task, they may also be replaced subsequently. Therefore, before clicking "Next," confirm that the file names, paths, and extensions meet expectations.
Step 3: Set Precise Text Search
After entering the Set Keyword Options page, first choose the search method. In the screenshot, "Precise Text Search" is selected. For inventory table field value replacement, precise search is usually more reliable because the old text itself is explicit, such as "Stationery" and "Warehouse C."
The page also displays the "Use Formula for Fuzzy Text Search" option, but it is not selected in this example. For most standardized cleanup tasks, it is recommended to use precise text search first to ensure a controllable replacement scope. If more complex matching needs arise later, consider other search methods based on the actual situation.
Step 4: Configure the Replacement List for Old and New Text
Enter the old text in the "Keywords to Find List" on the left. In the example, there are two lines: the first line is "Stationery," and the second is "Warehouse C." Enter the corresponding new text in the "Replacement Keywords List" on the right: the first line is "Office supplies," and the second is "Warehouse B."
The key here is "left-to-right correspondence." The software performs replacement according to the same row number relationship, replacing the left Line 1 with the right Line 1, and the left Line 2 with the right Line 2. When setting up, don't just check if the text is filled in; also check if the row numbers match.
For actual ledger cleanup work, it is recommended to first list the replacement mapping table. For example, old department names to new department names; old warehouse names to new warehouse names; old categories to new categories. After confirming the mapping relationship, copy it into the corresponding lists in the software to reduce input errors.
Step 5: Set Additional Options Based on Data Characteristics
In the screenshot, "Ignore Letter Case" and "Match complete word instead of part of the word" are checked. For English inventory tables, these two options are very useful. Ignoring case allows variations like Stationery and stationery not to affect the search; matching whole words avoids replacing only a part of a word, thus reducing erroneous modifications.
If your Excel files contain Chinese text, you can also judge based on the specific situation whether these options are needed. Whether processing Chinese or English, accuracy should be prioritized when setting batch replacement rules. Do not set overly broad matching ranges in pursuit of one-time completion, as this may affect content that shouldn't be modified.
Step 6: Enable Highlighting of Replaced Content
In the "Other Settings" at the bottom of the page, you can see the "Highlight replaced content" switch. Since this case requires highlighting replacement results, this option should be enabled. Once enabled, the software's processing results will mark the replaced cells with a yellow background, just like in the post-processing screenshot.
This setting is very suitable for inventory table and ledger cleanup. Because these files often need archiving or review by other personnel, the yellow highlighting serves as an intuitive prompt, helping reviewers quickly locate the changes made this time.
Step 7: Continue to Set Save Location and Start Batch Processing
After setting the find-and-replace rules and highlighting options, click "Next" at the bottom. The interface flow indicates that subsequent steps include "Set Save Location" and "Start Processing." It is recommended to save the processed files to a new folder, especially when batch-modifying important ledgers, as retaining the original files facilitates comparison and rollback.
After completing the save location setting, enter the start processing step. The software will execute the find-and-replace rules sequentially for the imported Excel file list. After processing finishes, open the output file to view the replaced new text and the yellow highlighting effect.
Common Issues and Precautions
1. Should I test before replacing?
It is recommended to select one or two representative files for testing first. Confirm whether "Stationery" is correctly replaced with "Office supplies," whether "Warehouse C" is correctly replaced with "Warehouse B," and check if the highlighting effect meets requirements. After the test is successful, process all files.
2. If the same old text appears in multiple columns, will it be replaced?
Judging from the function description and example effects, this function replaces keywords in Excel file content. As long as the content matches the search rules, it will be processed. Therefore, be cautious when setting keywords, ensuring that the old text indeed needs replacement in all its occurrence locations.
3. Will highlighting change the table's appearance?
Enabling "Highlight replaced content" adds a conspicuous background color to the replaced cells. This facilitates review but changes the cell display style. If the final file does not want to retain the highlight, decide whether to enable this based on business requirements, or adjust the style again after review.
4. Why set a separate save location?
Batch processing involves multiple files; saving results separately protects the original data. Especially for important materials like inventory tables, financial ledgers, and asset lists, it is recommended not to overwrite the original files directly on the first attempt. Retaining pre-processing versions helps quickly restore data if issues arise.
5. What about spaces in keywords?
In the example, "Warehouse C" and "Office supplies" both contain spaces. Enter the text completely as it actually appears, including intermediate spaces. When copying and pasting, be careful not to include extra leading or trailing spaces, as this could affect matching or replacement results.
Summary: Batch Replacement Plus Highlighting Makes Data Cleanup More Efficient
Batch replacement of specified text in Excel tables is a very common requirement in maintaining inventory tables, ledgers, and lists. Using HeSoft Doc Batch Tool , users can import multiple xlsx files at once, uniformly set the mapping between old and new text, and enable highlighting for replaced content. After processing, the old keywords in the files will be replaced by the new keywords, and the modified cells will be highlighted with a yellow background.
Compared to manual file-by-file operation, this method is more suitable for batch office work: it saves time, reduces repetitive labor, lowers the probability of missed corrections, and makes result review more intuitive. If you are cleaning up inventory tables, asset ledgers, product lists, or other Excel data files, prepare a replacement mapping table first, then follow the steps in this article. It is recommended to start testing with a small number of files, confirm the rules are accurate, and then batch process all Excel files.