Bulk find / replace…

Location on ribbon: Tab DVD Addin → Group Text and Number → Menu Text ops

Command ID: btnBulkFindReplace

What it does

The command opens the Bulk Find / Replace window, an upgrade over Excel's built-in Find & Replace. The key difference is the preview: you see the full list of cells that will change (sheet, cell address, before and after text) and decide which ones to apply.

It also supports wildcards and regular expressions, can run across every open workbook, saves a parameter set as a reusable preset, and includes a Σ Sum tool that totals the quantity column of the matching rows — ideal for answering "what is the total quantity of every line mentioning M250?".

How to use

1. Select a range if you want to work in a narrow scope, then go to tab DVD Addin → group Text and Number → menu Text ops → Bulk find / replace….
2. Fill in Find: and Replace:, then pick the match mode: Plain text, Wildcard (* ?) or Regex.
3. Choose the Scope: Selected range / Active sheet / Active workbook / All open workbooks, and Apply to: Values + formulas / Values only / Formulas only.
4. Tick the options you need: Case sensitive, Whole cell match, Whole word match.
5. Click 🔍 Preview. The result grid lists Sheet, Cell, Before, After, Unit and Value columns with a tick box on each row.
6. Use Select all / Deselect all, or highlight several rows and press Space to tick or untick them in bulk.
7. Click Replace all to apply the ticked rows. To keep the settings, click Save… next to Preset: and name it; next time pick the preset and click Load.

Example

An estimate workbook with 12 sheets uses the abbreviation "BTCT" where the printed submission to the client needs "Bê tông cốt thép".

Open the command, set Find BTCT, Replace Bê tông cốt thép, Scope Active workbook, Apply to Values only (so formulas stay intact), tick Whole word match. Click Preview — the grid lists 87 cells across 9 sheets. Scan the Before/After columns, untick the 3 rows that belong to the internal notes sheet, then click Replace all.

To total the quantities of the matched rows, type the unit column into Unit: (say D) and the quantity column into Sum col: (say E), then click Σ Sum. Use Export if you need the match list saved to a new sheet as a record.

Notes

• Replace all is disabled until you have run Preview and it returned matches.
• Any change to the parameters (search text, scope, options) invalidates the previous preview — run Preview again.
• Values + formulas and Formulas only edit inside formulas and can break references. When you only mean to change displayed text, choose Values only.
• The All open workbooks scope also touches other files currently open on the machine, so check before running.
• Replacements cannot be undone with Ctrl+Z. Save the file before clicking Replace all.

Related

• Smart Data Cleaning… — rule-based clean-up instead of manual find/replace.
• Add text — bulk prefixes and suffixes, which find/replace handles awkwardly.
• Remove extra spaces — normalise whitespace so search strings match exactly.
• Auto format — restore real number types after stripping stray characters from a number column.