Find and Replace in Excel (Ctrl+H) Tips

Find and replace in Excel changes every copy of a word, number or code in seconds. Press Ctrl+H (Mac: Control+H), type what to find and what to replace it with, and click Replace All. This guide is for beginners and anyone cleaning imported data: you'll learn the options that make replacing safe, wildcards, how to change formulas in bulk, and when a formula like TRIM does the job better.

How to use Find and Replace in Excel#

Find and Replace has two tabs in one dialog box. Find locates cells; Replace changes them.

To replace text:

1. Select the range to change. If you select just one cell, Excel searches the whole sheet.
2. Press Ctrl+H (Mac: Control+H).
3. Type the text in Find what and the new text in Replace with.
4. Click Replace All, or click Find Next and Replace to check each match one by one.

Excel tells you how many replacements it made. If the number looks wrong, press Ctrl+Z (Mac: ⌘Z) to undo them all at once. Our guide to editing, clearing and undoing in Excel covers undo in more depth.

Find and Replace options worth knowing#

Click Options >> in the dialog box to see the settings that control what counts as a match.

Match entire cell contents is the one that prevents most accidents. Without it, Excel replaces the text wherever it appears inside a cell.

Why Replace All can break your data#

Replace All changes every partial match, which can quietly damage numbers and words. Imagine column C holds quantities, and the code 0 was used for "unknown":

The middle column shows the problem: every 0 inside a number disappears too. Ticking Match entire cell contents only empties cells that are exactly 0.

The same thing happens with text. Replacing "Ann" with "Anna" also turns "Anne" into "Annae". Check Find All first, then replace.

Use wildcards in Find and Replace#

Wildcards let one search match many variations:

• * matches any number of characters. Cobalt* finds "Cobalt", "Cobalt Inc" and "Cobalt Ltd".
• ? matches exactly one character. P-10? finds "P-101" to "P-109", but not "P-1001".
• ~ turns a wildcard back into a normal character. ~* finds a real asterisk.

Wildcards work in Replace too. With Match entire cell contents on, replacing Cobalt* with Cobalt Inc makes every spelling of that customer the same, which is useful before you build a pivot table or a SUMIF. The same symbols work in formulas: see Excel wildcards in COUNTIF and SUMIF.

Find All: select every matching cell#

Find All lists every match at the bottom of the dialog box, with its sheet, cell and value. Click a row to jump to that cell.

Press Ctrl+A (Mac: ⌘A) while the list has focus, and Excel selects all the matching cells on the sheet. Close the dialog and you can format, delete or fill all of them in one step. For example, find every cell that says "Overdue" and colour them red, or find every "N/A" and press Delete.

Replace text inside formulas#

Replace searches inside formulas, not only text and numbers, which makes it a fast way to update many formulas at once.

A common case: a summary sheet points at a sheet called Jan, and next month you copy it for February.

=SUM(Jan!B2:B50)

Select the formulas, press Ctrl+H, find Jan! and replace with Feb!. Every formula now reads =SUM(Feb!B2:B50). Including the ! stops Excel from touching the word "Jan" anywhere else, such as in a label.

You can also swap a reference, such as $G$1 for $G$2, when a rate moves to a new cell. Check the result by clicking one formula. If references are new to you, start with the guide to Excel cell references.

Find and Replace vs TRIM and other formulas#

Find and Replace is best for exact, one-off changes. Formulas are better when the problem is a pattern. Spaces are the classic example:

• Replacing a space with nothing removes every space, so "DL 101" becomes "DL101".
• Replacing two spaces with one shrinks double spaces, but can't remove a single space at the start or end.
• =TRIM(A2) removes spaces at the start and end and shrinks repeated spaces inside to one, in one step.

The TRIM function guide has more examples, and the guide to cleaning messy data combines TRIM with CLEAN and PROPER. When you need a formula version of Replace, SUBSTITUTE does the same job inside a cell: =SUBSTITUTE(A2," kg","") removes " kg" from a weight.

Two more tricks: on Windows, press Ctrl+J in the Find what box to search for line breaks inside cells, and replace them with a space. After a big clean-up, freeze the header row so you can scroll through the results and check them, and if you need to add a helper column for TRIM, see how to insert and delete rows and columns fast.

Practise it#

Find and Replace is an editing skill, and undo is your safety net. Practise both in Edit, clear and undo, then fix stray spaces, random capitals and separators with formulas in Cleaning messy text. The exercise on this page cleans a code that Find and Replace alone can't fix.