Find and Replace in Excel: Complete Guide, Wildcards

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

Find and Replace in Excel locates any text, number or format in a sheet or workbook and, when you ask it to, swaps every match for something else in one pass. Press Ctrl+F to find and Ctrl+H to replace. This lesson explains both tabs, every setting behind the Options button, and the wildcards * and ? that strip or keep part of a cell.

Course chapters

This page is lesson 0 of the Find and Replace module. Work through it first, then take the chapters in order.

Go To Special: select blanks, formulas and visible cells – Find All as a selection tool, Go To, and every Go To Special option.
Wildcards: 10 patterns with *, ? and ~ – remove text before or after a character, delete brackets, strip line breaks with Ctrl+J.
Find and replace formatting – fill colours, fonts, number formats and cell styles, without touching the values.
Replace inside formulas safely – change sheet names, references, ranges and function names without breaking the model.
Find and replace across sheets and workbooks – Within: Workbook, grouped sheets and what the dialog does not search.
Find and replace with formulas and VBA – SUBSTITUTE, REPLACE, TEXTSPLIT and the Range.Replace method.

Download the course practice file (a small employee table with EMP ID, Name, Designation and Salary) to follow every example.

Where Find and Replace lives

The command sits on Home > Editing > Find & Select, but almost everyone uses the keyboard. Ctrl+F opens the Find tab and Ctrl+H opens the Replace tab of the same dialog; both tabs share the options described below. The dialog is modeless, so you can keep it open while you click and type in the sheet.

Selection matters. Select a range first and Excel searches only that range. With a single cell selected it searches the whole sheet, or the whole workbook if you change Within. Searching starts from the active cell and wraps around, which is why Find Next sometimes appears to start in the middle of the data.

The Find tab

Find what holds the text or number to look for. The drop-down remembers your recent searches.
Find Next jumps to the next match each time you click it (or press Enter). Shift+Enter goes backwards.
Find All lists every match with its workbook, sheet, cell address, value and formula. Click a row to jump to that cell, click a column header to sort the list, or press Ctrl+A inside the list to select every matching cell in the sheet at once.

Find All results list in Excel showing every matching cell with its sheet, cell address and value — Find All lists every match

The Options button

Click Options >> to expand the dialog. Excel remembers these settings for the rest of the session, which explains most “Excel cannot find the data you are searching for” surprises: an option ticked an hour ago is still filtering the search now.

Option	What it does	When to use it
Within	Searches the current Sheet or the whole Workbook	Workbook when a code or name appears on many tabs
Search	By Rows (left to right, then down) or By Columns (top to bottom, then right)	Only changes the order in which Find Next visits matches
Look in	Formulas (the formula text), Values (the displayed result) or Notes	Values to find a calculated result; Formulas to find references. Replace only works with Formulas
Match case	Makes the search case sensitive	Codes such as “pk” versus “PK”
Match entire cell contents	Finds only cells that contain nothing but the search text	Replacing short values such as 1 or A that also appear inside longer values
Format	Restricts the search to cells with a given fill, font, border or number format	Counting or re-colouring highlighted cells; see chapter 3

Within

Search the current Sheet or the whole Workbook. Workbook searches every sheet, including hidden ones, but not headers, footers, chart titles or shapes.

Search

By Rows moves left to right and then down; By Columns moves top to bottom and then right. It changes only the order in which Find Next visits matches, never which cells are found.

Look in

Formulas searches the formula text and the constants typed into cells, Values searches the displayed result, and Notes (Comments in Excel 2016 and earlier) searches cell notes. A cell showing 100 from =A1*2 is found under Values but not under Formulas. The Replace tab only offers Formulas, because Excel cannot rewrite a calculated result.

Match case

Makes the search case sensitive, so “agent” no longer matches “Agent”. Leave it off for ordinary text clean-up and on when upper and lower case carry different meanings, such as product codes.

Match entire cell contents

Finds only cells whose whole content equals the search text, so “age” no longer matches “Agent”. Tick it before replacing short values such as 1, A or Yes; without it, replacing 1 with 2 also turns 10 into 20 and 2019 into 2029.

Format

Restricts the search to cells with a given fill, font, border or number format, or pick Choose Format From Cell to copy the format of an existing cell. On the Replace tab a second Format button replaces one format with another without changing any values. Use Clear Find Format when you are done, otherwise later searches silently ignore unformatted cells. Chapter 3 covers formats in depth.

The Replace tab

Ctrl+H adds a Replace with box and two buttons. Replace changes the current match and moves to the next one, so you can approve matches one by one. Replace All changes every match in the selection, sheet or workbook and reports how many replacements it made. All the Find options apply, and Look in is fixed to Formulas.

Replace tab of the Excel Find and Replace dialog with the Replace with box and the Replace All button — The Replace tab (Ctrl+H)

Worked example: fix a typo in the practice file

Open the practice file. Column C (Designation) contains “Assitant Manager” twice, a spelling mistake that breaks any COUNTIF or pivot table built on it.

EMP ID	Name	Designation	Salary
504424	PK	Team Leader	3,284
417905	Victor	Assitant Manager	4,866
526956	King	Manager	5,175
517046	Laura	Agent	2,593
561147	Nadir	Assitant Manager	4,546

Click any cell in column C and press Ctrl+F. Type Assitant and click Find All. The list shows 2 cells, which confirms the scope before anything changes.
Switch to the Replace tab. Find what already holds Assitant; type Assistant in Replace with.
Click Replace All. Excel reports “All done. We made 2 replacements.”
Now search for Agent with Find All: 4 cells are found (C6, C8, C9, C10), matching the count you would get from =COUNTIF(C:C,"Agent").

Wildcards: keep the name or keep the ID

* stands for any number of characters and ? for exactly one character. Wildcards work only in Find what; whatever you type in Replace with is inserted literally. Column A below holds “Name – EMP ID” in one cell, separated by a space, a hyphen and a space.

Excel column with employee name and EMP ID combined in one cell, separated by a hyphen — Name and EMP ID in one cell

Keep only the name

Select the range in column A and press Ctrl+H.
In Find what type a space, a hyphen and an asterisk: -*.
Leave Replace with empty.
Click Replace All. Everything from the hyphen to the end of the cell is deleted.

Excel Replace dialog with the wildcard pattern space hyphen asterisk in Find what and an empty Replace with box — Wildcard pattern to remove the ID

Column A in Excel showing only the employee names after the wildcard replace — Only the names remain

Keep only the ID

Repeat the steps with * - (asterisk, space, hyphen) in Find what and click Replace All. Everything up to and including the hyphen is removed and the IDs remain. Because the result is text, convert it with Data > Text to Columns > Finish or =VALUE(A2) if you need numbers.

Excel Replace dialog with the wildcard pattern asterisk space hyphen to remove the name — Wildcard pattern to remove the name

Column A in Excel showing only the employee IDs after the wildcard replace — Only the IDs remain

Chapter 2 lists ten more wildcard patterns, including how to remove line breaks and how to find a literal asterisk.

Tips and common mistakes

Replace All has no preview. Run Find All first to check the match count, and keep Ctrl+Z ready; one Undo reverses the whole Replace All.
Select the range before you replace. With one cell selected, Replace All rewrites the entire sheet, including cells you never looked at.
To find a literal * or ? put a tilde in front: ~* and ~?. A literal tilde is ~~.
Remove line breaks by pressing Ctrl+J in Find what. The box looks empty but contains the line-feed character.
Replace in formulas with care. Look in is fixed to Formulas on the Replace tab, so changing “Sheet1” to “Sheet2” rewrites every reference, not just typed text. See chapter 4.
Reset the options if Excel cannot find obvious text. Match entire cell contents, Match case or a leftover Format filter is usually the cause.
Numbers can become text. A wildcard replace on 12,345 leaves a text string; convert it afterwards.

Errors and how to fix them

Message or symptom	Cause	Fix
“We couldn’t find what you were looking for”	Match entire cell contents, Match case or a Format filter is still on; or Look in is Formulas while the text is a calculated value	Click Options, untick the boxes, choose Format > Clear Find Format, set Look in to Values
Replace All changed far more cells than expected	Only one cell was selected, so the whole sheet was searched, or a short Find what matched inside longer values	Press Ctrl+Z, select the range, tick Match entire cell contents
Formulas now show #REF! or #NAME?	The replacement rewrote references or function names inside formulas	Ctrl+Z, then restrict the selection to constants with Go To Special (chapter 1)
Dates or numbers turned into text	A wildcard replace stores the result as text	Use Text to Columns, VALUE or Paste Special > Multiply by 1

Practice exercise

In the practice file, use Find All to count how many employees are Managers, then Team Leaders.
Replace every “Agent” with “Sales Agent” using Match entire cell contents, and check that “Assistant Manager” is untouched.
Combine Name and EMP ID in column F with =B2&" - "&A2, paste as values, then strip the ID back out with the -* pattern.
Tick Match case, search for “pk” and note the result; untick it and search again.
Set Look in to Values, search for a salary that only appears as a calculated total, then switch to Formulas and see why it is not found.

Key takeaways

Ctrl+F finds, Ctrl+H replaces, and both share the same Options.
Find All shows the scope before you change anything, and Ctrl+A in its list selects every match.
Options are sticky for the session; clear them when a search fails unexpectedly.
Match entire cell contents protects short values from partial matches.
The wildcards * and ? work only in Find what, and a tilde makes them literal.
Select the range first; Replace All never asks for confirmation.

Related lessons

Frequently asked questions

What is the shortcut for Find and Replace in Excel?

Ctrl+F opens the Find tab and Ctrl+H opens the Replace tab of the same dialog. Both are available on Home, Editing, Find & Select. The dialog stays open while you work, and F4 repeats the last edit, so Ctrl+H followed by Replace All is the fastest way to clean a column.

How do I find and replace across all sheets in a workbook?

Press Ctrl+H, click Options and set Within to Workbook, then click Replace All. Excel searches every sheet, including hidden ones. Alternatively right-click a sheet tab, choose Select All Sheets and run the replace on the grouped sheets, which is safer when you want to skip a few tabs.

How do I remove everything after a certain character in Excel?

Press Ctrl+H, type the character followed by an asterisk in Find what (for example ,*), leave Replace with blank and click Replace All. The asterisk matches the character and everything after it, so only the text before the comma remains in each cell.

Why does Find and Replace say it cannot find what I am looking for?

An option is still set from an earlier search: Match entire cell contents, Match case, a Format filter, or Look in set to Formulas while the text is only a displayed value. Click Options, untick every box, choose Format, Clear Find Format and set Look in to Values.

Can Find and Replace change part of a formula?

Yes. On the Replace tab Look in is fixed to Formulas, so replacing Sheet1 with Sheet2 or A1 with B1 rewrites every formula that contains that text. Run Find All first to see how many formulas are affected, and select only constants with Go To Special if formulas must stay untouched.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.