Search and Replace Multiple Item Numbers Across Excel Files at Once
Question details
The user needs to find a specific list of discontinued item numbers across hundreds of spreadsheets and replace their corresponding prices in bulk without manually searching each file.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Updating large datasets by searching for multiple distinct values across numerous workbooks and replacing adjacent data.
- Observed behavior
- Excel's standard Find and Replace command only allows searching for one term at a time, making it too tedious to process a list of 25 items across hundreds of files.
Before running bulk search and replace operations across hundreds of files, ensure you have consolidated the target workbooks into a single folder and prepared a separate Excel sheet containing your list of search terms.
Use a VBA Macro for Bulk Search and Replace
A custom VBA script can iterate through a folder of workbooks, search for multiple item numbers, and automatically update the corresponding prices.
Since the standard Find tool only handles one keyword, VBA (Visual Basic for Applications) is the most effective native method for scanning hundreds of files for an array of items simultaneously.
Open a new workbook and list your 25 discontinued item numbers in Column A, and the replacement text 'DISC' in Column B.
Press Alt + F11 to open the VBA Editor. Go to Insert > Module to create a blank script window.
Paste a VBA script designed to loop through all files in a specific folder path (using the Dir function), open each workbook, and use the Find method to locate each item from your reference list and offset the value to change the price.
Press F5 or click the Run button. The macro will process all the files in the target directory and save the changes.
Consolidate and Update Data Using Power Query
If you need a combined report rather than modifying the original files, Power Query can pull all data into one table and apply a bulk update via a merge.
Utilize a Third-Party Excel Add-in
Specialized third-party add-ins offer user-friendly interfaces for multi-item find and replace across multiple workbooks.
Handle Bulk Data Efficiently with WPS Spreadsheet
WPS Office provides robust spreadsheet capabilities, including VBA and macro support in advanced editions, allowing you to run powerful scripts for multi-file operations just like Excel.
- 1. Download and Install: Download WPS Office and install it on your computer.
- 2. Open WPS Spreadsheet: Launch the application and prepare your lookup list of discontinued items.
- 3. Access Developer Tools: Navigate to the Developer tab to open the VBA Editor and insert your batch-processing script.
- 4. Run Macros: Execute the script to automate the search and replace process across your entire directory.
Frequently Asked Questions
Can I search for multiple items using Excel's built-in Find tool?
No, the standard Find and Replace tool (Ctrl + F / Ctrl + H) in Excel only supports searching for one specific text string or value at a time.
Will a VBA macro overwrite my original Excel files?
Yes, if the VBA macro includes a save command (such as Workbook.Save) inside its loop, it will permanently overwrite the original files. It is strongly advised to duplicate the folder as a backup before executing the macro.
Can Power Query edit my original files directly?
No, Power Query is non-destructive. It extracts data from your source files, transforms it, and outputs it into a new consolidated table or data model, leaving your original files untouched.
Is there a formula to replace multiple items in a single sheet?
If you only need to process a single sheet, you can use the XLOOKUP or VLOOKUP function in an adjacent column to cross-reference your list of items. If a match is found, the formula outputs the new text; if not, it outputs the original price. You can then copy and paste the new column as values over the old one.
