Bulk Find and Replace From Table

What it does

Excel's Find and Replace dialog handles one pair at a time. When you have 30 substitutions to apply (vendor name normalizations, country code conversions, account remapping), running Find and Replace 30 times is tedious and you lose count of which ones you've done. The macro reads a 2-column table where column 1 is "find" and column 2 is "replace", then walks down the table applying every pair to your data range in one pass. Click your data, run the macro, click the lookup table, and 30 replacements happen in seconds.

Real-world example

Picture a senior accountant at a tech holding company that's mid-migration from one general ledger system to another. The old GL used 3-letter account codes (REV, COG, OPX); the new GL uses 6-character codes (410100, 510100, 610100). She has 14 months of historical journal entries, about 84,000 rows total, that need every old code remapped to its new code before they get loaded into the new system. The mapping table has 47 entries.

Her current method: open Find and Replace, type the first old code, type the new code, click Replace All. Move to the next mapping. Repeat 47 times. About 15 seconds per pair if she doesn't mistype. So roughly 12 minutes of Find/Replace clicking, with the cognitive load of remembering which pairs she's already done. Twice during the project she lost track and re-applied a pair, which silently broke the data because the second replacement saw a value that no longer existed and skipped quietly.

With this macro she keeps the mapping table on a tab called CodeMap (column A = old code, column B = new code), selects the journal entries data, runs the tool, clicks the CodeMap range when prompted, and 47 replacements happen across 84,000 rows in about 6 seconds. The popup confirms "Applied 47 find/replace pair(s) to your data range." She moves on to the next month.

On a 14-month migration with 47 mapping pairs and ~84,000 rows of journal entries, expect about 2 hours saved versus running Find/Replace by hand. The bigger win is no double-application errors because every pair runs exactly once.

How to use

First time? See How to run our tools for the 30-second setup.

1. Build (or open) a 2-column lookup table somewhere in the workbook. Column 1 is the value to find, column 2 is the value to replace it with. Put the data on a separate sheet to keep it out of the way.
2. Select the data range where you want the replacements applied.
3. Press Alt + F8 (Windows) or Option + F8 (Mac), pick BulkFindReplaceFromTable, click Run.
4. When prompted, click to select the lookup table. Skip the header row of the lookup table if it has one (drag from the first data row).
5. The macro applies every pair and reports the count.

FAQ

Does it match whole words or partial text?

Partial match by default (xlPart), case-insensitive. So a "find" entry of Acme matches Acme Co, acme corporation, and Acme.io. If you specifically need whole-cell matches, edit the .bas source and change LookAt:=xlPart to LookAt:=xlWhole. Whole-cell matches are safer for code-mapping use cases where partial matches would corrupt longer codes.

What if the order of pairs matters?

The macro applies pairs in the order they appear in the lookup table, top to bottom. So if pair 5 introduces text that pair 12 would match, pair 12 sees the new text and replaces it. For most use cases this isn't an issue. When it is, reorder your lookup table so the dependent pairs come in the right sequence, or run the macro twice with different lookup tables.

Can I include blank "replace" entries to delete text?

Yes. Leave column 2 blank for any row where you want the matching find text simply removed. The macro replaces the find with an empty string. Blank "find" entries are skipped (the macro requires a non-empty find).

Will it search inside formulas?

No. The macro searches xlValues only. Formula text is not modified; only displayed values get replaced. If you need to rewrite formulas (changing a sheet reference, for example), use Excel's built-in Find and Replace with LookIn: Formulas.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running undoes all replacements at once. As with any bulk edit, work on a copy of the file if it's critical. The macro runs entirely offline. Your data never leaves your machine.