Bulk Find / Replace

An estimate workbook usually carries a dozen sheets typed by several people: the same material is abbreviated on one sheet and spelled out on another, item codes carry the wrong prefix, work names drag a parenthetical note behind them. Excel's Ctrl+H can fix all of that — but it never shows you which cells it is about to touch, and in a document going to the client that is a real risk.

Bulk find / replace (tab DVD Addin → group Text and Number → menu Text ops) adds the missing step: preview every cell that will change, untick the ones you want left alone, and only then replace.

Commands in this group

Command	What it does
Bulk find / replace…	Find and replace with a preview grid, wildcard and regex modes, multi-sheet and multi-workbook scope, saved presets
Smart Data Cleaning…	Rule-based clean-up: trim, case, Vietnamese encoding repair, dates, phone numbers, duplicate rows
Remove extra spaces	One-click whitespace clean-up — run it first so search strings match exactly
Add text	Bulk prefixes and suffixes, which find/replace handles awkwardly

Pick by the kind of defect: Bulk find / replace for keyword-specific edits, Smart Data Cleaning for systematic ones (whitespace, casing, broken encoding, duplicate rows).

A safe replacement run

1. Save the file first — replacements cannot be undone with Ctrl + Z.
2. Select a range if you want a narrow scope, then open DVD Addin → Text and Number → Text ops → Bulk find / replace….
3. Fill in Find: and Replace:, then choose the match mode: Plain text, Wildcard (* ?) or Regex.
4. Choose the Scope: Selected range / Active sheet / Active workbook / All open workbooks, and Apply to: Values + formulas / Values only / Formulas only.
5. Tick what you need: Case sensitive, Whole cell match, Whole word match.
6. Click 🔍 Preview. The grid lists Sheet, Cell, Before, After, Unit and Value columns with a tick box on each row.
7. Scan the Before / After columns, then use Select all / Deselect all, or highlight several rows and press Space to tick them in bulk.
8. 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

A 12-sheet estimate uses the abbreviation BTCT where the printed client submission needs Bê tông cốt thép. Settings: 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 — 87 cells across 9 sheets:

✓	Sheet	Cell	Before	After
✓	BoQ L3	C41	Đổ BTCT dầm sàn	Đổ Bê tông cốt thép dầm sàn
✓	BoQ L3	C58	Cốp pha BTCT cột	Cốp pha Bê tông cốt thép cột
☐	Internal notes	B7	BTCT — internal shorthand	Bê tông cốt thép — internal shorthand

Untick the three rows on 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) and click Σ Sum; Export writes the match list to a new sheet for the record.

A few regex patterns that earn their keep on a BoQ:

Job	Find (Regex)	Replace
Strip a trailing note in parentheses	\s*\(.*?\)\s*$	(empty)
Collapse runs of spaces into one	\s{2,}	(a single space)
Change an item-code prefix	^AB\.	AF.
Flip MM/DD/yyyy into DD/MM/yyyy	(\d{2})/(\d{2})/(\d{4})	$2/$1/$3

Tips and caveats

• Replace all stays disabled until Preview has run and returned matches. Any parameter change invalidates the previous preview — run Preview again.
• Values + formulas and Formulas only edit inside the formulas themselves 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 — close what you do not want edited before running.
• In the Replace: box, $ is a special character ($1, $2 insert regex capture groups). To insert a literal $, type $$.
• Wildcard mode understands only * (any run of characters) and ? (a single character); every other character is treated literally.
• Presets are stored per Windows account (in the registry), not inside the Excel file — so they are available in every workbook on that machine, but they do not travel with a file you send to someone else.

See also

• Smart Data Cleaning — for systematic defects rather than keyword-specific ones.
• Text Utilities — add text, remove accents, auto-format numbers and dates.
• Quick Input — stop the variants at data-entry time so there is less to find and replace later.