How to find and replace multiple values in Excel in bulk

You may also be interested in: the REPLACE function in Excel.

When working with text data in Excel, it’s often necessary to find and replace multiple values with their corresponding alternatives. Sometimes you need to replace words with other words (e.g., synonyms), sometimes several characters with other characters.

The more such find-and-replace operations you have to perform, the more complex the task becomes—and the more important it is to know the tools that can simplify and speed up the process. In this article, I’ll cover all the tools I know, including ones I created myself.

Author of the !SEMTools add-in and creator of this site, Dmitry Tumaykin

Find and Replace in Excel

The Find and Replace feature is one of the most frequently used in Excel. The built-in version allows replacing just one value at a time, but in many different ways. Let’s see how to work with it efficiently.

Keyboard Shortcuts

These key combinations will significantly speed up your workflow:

• Open the Find dialog — Ctrl + F
• Open the Find and Replace dialog — Ctrl + H
• Select all found cells (after clicking “Find All”) — Ctrl + A
• Clear all found cells — Ctrl + Delete
• Enter the same value in all found cells — type your text, then press Ctrl + Enter

Example: here I find specific cells and then edit them directly. Unlike replacing, editing found cells lets you quickly change their content entirely.

When Find and Replace Doesn’t Work

I’ve been stuck in this situation myself: you’re sure the target text exists, but Excel shows a message like:

or during replacement:

The fix: click “Options” in these dialogs. Chances are “Match case” or “Match entire cell contents” is checked, which prevents Excel from finding the target. Excel remembers the last search settings.

Wildcards – How to Find “*”

Excel’s official help says:

• You can use wildcards * and ?
• * means any number of characters (including none); ? means any single character
• They can be used in Find and Replace

What it doesn’t say is that with “Match entire cell contents” turned on, you can also:

• Find cells ending with a certain character, word, or text
• Find cells starting with a certain character, word, or text
• Find non-empty cells

Example: find all two-digit numbers, then numbers ending or starting with 7, and finally all non-empty cells. Remember: Ctrl + A selects all found results.

To search for a literal “*” character, type ~* (tilde before the asterisk). The tilde is usually under the Esc key. This also works for ?.

Replace Multiple Values with Multiple Values

Bulk replacement is a common need. Unfortunately, Excel doesn’t have a simple built-in tool for it. Depending on your needs, you can use formulas, regular expressions, or the !SEMTools add-in.

Bulk Replacement with SUBSTITUTE

By nesting SUBSTITUTE functions, you can replace multiple values at once (up to 64 levels deep). For example, here’s how to convert Cyrillic to Latin:

Using an empty string ("") as the replacement value effectively deletes characters—see how to delete numbers from a cell this way.

Bulk Replacement with Regular Expressions

Note: Excel doesn’t support RegEx natively, but these formulas are available for free with the !SEMTools add-in.

=regexreplace(A1,"\d","#")
=regexreplace(A1,"\w","#")
=regexreplace(A1,"[A-Z]","#")
=regexreplace(A1,"\s","_")

These replace all numbers, all Latin letters, all uppercase letters, or all spaces/tabs/newlines respectively.

Bulk Replacement in !SEMTools

The !SEMTools add-in lets you replace:

• Characters or strings
• Regular expression patterns
• Words
• Entire cell values (like an advanced VLOOKUP)

Simply select your range, choose the procedure, and specify two columns with “find” and “replace” lists.

Example: Character Replacement by Occurrence

Example: Replacing a List of Words with Another List

See also:

• Find uppercase/lowercase letters in a cell
• Find Latin characters
• Find numbers in text cells
• Find text characters
• SUBSTITUTE function
• REPLACE function
• Find specific characters in Excel cells

This post is also available in RU.