Use Excel Office Scripts to Replace Text on Multiple Worksheets
Question details
Replace specified text on selected Excel worksheets using one Office Script without manually activating individual sheets.
- Product
- Microsoft Excel Office Scripts
- Device & OS
- not provided
- Scenario
- The user needs to update "A.REY" to "A. REY" only on the ORANGE and BLACK worksheets.
- Observed behavior
- The replacement should run on each matching worksheet’s used range and skip sheets that do not contain a used range.
Confirm that the target worksheet names are exactly ORANGE and BLACK, including any spaces or punctuation. Open the workbook in Excel for the web and make sure the Automate tab is available.
Filter Target Worksheets and Replace Text in Their Used Ranges
Use this Office Script to replace text on only the ORANGE and BLACK worksheets without selecting or activating either sheet.
The script filters the workbook’s worksheet collection by name, then checks whether each selected sheet has a used range before calling replaceAll. This prevents an error when a target sheet is empty.
In Excel for the web, open the workbook, select the Automate tab, and choose New Script.
In the Code Editor, replace the existing code with: function main(workbook: ExcelScript.Workbook) { const sheets = workbook.getWorksheets().filter(sheet => sheet.getName() === "ORANGE" || sheet.getName() === "BLACK"); sheets.forEach(sheet => { const usedRange = sheet.getUsedRange(); if (usedRange) { usedRange.replaceAll("A.REY", "A. REY", { completeMatch: false, matchCase: false }); } }); }
Select Run in the Office Scripts editor. Excel searches the used range on the ORANGE and BLACK worksheets and changes each occurrence of "A.REY" to "A. REY".
Edit Excel Workbooks Easily with WPS Office Spreadsheets
Office Scripts are a Microsoft Excel automation feature. If you need a free, lightweight spreadsheet application for everyday workbook editing and migration, WPS Office provides a familiar interface and strong compatibility with common Microsoft Office file formats.
Frequently Asked Questions
Why does the script check whether getUsedRange returns a range?
A worksheet can be empty and have no used range. Storing sheet.getUsedRange() in a variable and checking if (usedRange) prevents replaceAll from running on an undefined range.
How can I add another worksheet to the Office Script?
Add another name test inside the filter condition, such as || sheet.getName() === "GREEN". The script will then process the GREEN worksheet along with ORANGE and BLACK.
Does completeMatch false replace text inside a longer cell value?
Yes. With completeMatch: false, Excel replaces matching text wherever it appears within the cell value. Set completeMatch: true if the entire cell must exactly equal "A.REY".
Can the script replace text on every worksheet in the workbook?
Yes. Remove the filter and use workbook.getWorksheets().forEach(...). Keep the usedRange check so empty worksheets are skipped safely.
Will the replacement distinguish uppercase and lowercase letters?
Not with matchCase: false. The current script treats uppercase and lowercase versions of the search text as matches. Change it to matchCase: true for case-sensitive replacement.
