Find and Replace in Excel: Wildcards, Tricks, and Power Workflows 2026 October
Find and Replace in Excel — Ctrl+F, Ctrl+H, wildcards, match-case, search options, line-break tricks, and the workflows that make Excel cleanup fast. 🟢
To find and replace in Excel, press Ctrl+H, type the text to find in Find what and the new text in Replace with, then click Replace All (or Replace to go one match at a time). Click Options to search the whole workbook, match case or whole cells only, or use the wildcards * and ?.
This guide covers Find and Replace from the basics through the advanced: the keyboard shortcuts, the Options pane, wildcard syntax (asterisk, question mark, tilde escape), line breaks and other invisible characters, the difference between Replace and Replace All, and the workarounds when the built-in tool isn't enough, such as SUBSTITUTE formulas, VBA macros, or Power Query.
The most useful upgrade for most users is learning the wildcards. The asterisk (*) matches any number of characters, the question mark (?) matches exactly one character, and the tilde (~) escapes a literal asterisk or question mark when you need to search for one as text. Combined with Match case and Match entire cell contents, wildcards find patterns that plain text searches can't catch.
On Windows, Ctrl+F opens the Find tab and Ctrl+H opens the Replace tab of the same dialog. On a Mac, Microsoft lists Ctrl+H or Cmd+Shift+H for Replace and Ctrl+F or Shift+F5 for the Find dialog, while Cmd+F opens the search box. Click Options to expose the deeper settings.
Excel doesn't support full regular expressions in the standard dialog. The asterisk and question mark wildcards cover most everyday cleanup; for real regex-style transformations, use Power Query or a short VBA macro, both covered later in this guide.
Find and Replace at a glance
Find: Ctrl+F. Replace: Ctrl+H (Windows); on Mac, Ctrl+H or Cmd+Shift+H. Open Options pane: click Options in the dialog. Wildcards: * = any characters, ? = single character, ~ = escape (search for literal * or ?). Search options: Match case, Match entire cell contents, Within Sheet or Workbook, Search By Rows or By Columns, Look in Formulas / Values / Notes / Comments. Line breaks: press Ctrl+J in the Find what field (Windows) to enter a line-feed character.
The basics — Ctrl+F and Ctrl+H
Press Ctrl+F to open the Find tab. Type the text you want to find and click Find Next or Find All. Find Next steps through matches one at a time; Find All lists every match with its cell reference at the bottom of the dialog, and clicking an item in the list jumps to that cell.
Press Ctrl+H to open the Replace tab of the same dialog. Type the text to find in Find what and the replacement in Replace with. Click Find Next to step through matches, Replace to replace the current match, or Replace All to replace every match at once. Excel reports the number of replacements made, which is the quickest way to verify that the search caught the cells you expected.
By default, Find and Replace operates on the active worksheet only. Click the Options button to expose the broader settings: Within (Sheet or Workbook), Search (By Rows or By Columns), Look in (Formulas, Values, Notes, Comments), Match case, Match entire cell contents, and Format.
Selection matters. If you select a range of cells before opening the dialog, the search runs only within that selection. If a single cell is selected, it searches the whole active sheet (or the whole workbook when Within is set to Workbook). Selecting the column or table you want to clean up before running Replace All keeps unrelated cells safe.
Find and Replace — key options
When checked, the search distinguishes between upper and lower case. "Apple" won't match "apple". When unchecked (default), case is ignored. Useful for cleanups where you want to standardize capitalization without affecting cells already in the right format. Often combined with Match Entire Cell Contents to make case-sensitive replacements highly targeted.
When checked, the search only matches cells whose entire contents equal the Find what value. Use it to replace a specific cell value without touching cells that merely contain that string inside longer text. Without it, Find what is treated as a substring search that matches anywhere inside the cell.
Sheet (default) searches only the active worksheet. Workbook searches every sheet in the open workbook. Workbook scope is useful for finding all instances of a value across many tabs at once — for example, replacing a deprecated product code that appears on multiple sheets. Choose carefully because Replace All in Workbook scope affects every sheet immediately.
Controls the order Excel walks through cells. By Rows steps left-to-right then top-to-bottom (the default). By Columns steps top-to-bottom then left-to-right. The order matters for Find Next stepping through matches in a predictable sequence. Most users leave this on the default unless they have a column-oriented data structure where column-by-column makes more sense.
Formulas searches the underlying cell content (constants and formula text) and is normally the default; Values searches what is displayed after calculation; Notes and Comments search the text of notes and threaded comments. If a number you can see is not found, check this setting. The Replace tab works on formulas and does not offer the Look in choice.
Click the Format button next to Find what (or Replace with) to search for or apply formatting such as font, fill, and border instead of, or together with, text. You can pick a format from a cell or set it manually, and it is useful for finding every cell with a specific fill color and changing it.
Find and Replace options at a glance
This table summarizes every setting in the Find and Replace dialog and what each one does.
| Option | What it does | Notes |
|---|---|---|
| Find what / Replace with | Text, numbers, or a wildcard pattern to find, and the literal text to put in its place | Replace with is always literal; it cannot reuse matched parts like regex |
| Within | Sheet (active worksheet, default) or Workbook (every sheet) | Replace All in Workbook changes every sheet at once |
| Search | By Rows or By Columns | Sets the order Find Next walks through cells |
| Look in | Formulas, Values, Notes, or Comments | Formulas matches underlying content, Values the displayed result; Replace works on formulas |
| Match case | Distinguishes "Apple" from "apple" | Off by default |
| Match entire cell contents | Matches only cells whose whole content equals Find what | Off by default; without it, "100" also matches 1000 |
| Format | Finds or applies font, fill, border, and other formatting | Can be used with or without text |
| * (asterisk) | Any number of characters, including none | Works in Find what only |
| ? (question mark) | Exactly one character | Works in Find what only |
| ~ (tilde) | Escapes a literal *, ?, or ~ (for example ~* finds an asterisk) | Place it directly before the character |
| Ctrl+J | Enters a line-feed (line break) character in Find what or Replace with | Windows shortcut; the field looks empty afterward |
Wildcards — the feature most users miss
Excel's Find and Replace supports two wildcard characters that work just like in classic Windows file searches. The asterisk (*) matches any number of characters (including zero). The question mark (?) matches exactly one character. Combined with Match Entire Cell Contents, wildcards let you target patterns rather than literal strings. For example, searching for *@gmail.com finds every cell ending with the @gmail.com domain. Searching for 1??? with Match Entire Cell finds every four-character cell starting with 1.
The tilde (~) escapes a literal asterisk or question mark when you need to search for one as text. Without escaping, Find What for 5*3 would match anything starting with 5 and ending with 3. With 5~*3, Find What matches the literal string "5*3". Same for question marks: ~? matches a literal question mark. The tilde escape only matters when your search target actually contains an asterisk or question mark in its literal form.
Wildcards work in both Find What and the Find Next stepping. They do not work in Replace With for substitution patterns the way regex backreferences would. Excel's Replace With is treated as a literal string, so you can't reuse parts of the matched pattern in the replacement the way you would in a regex tool. For pattern-aware replacements, use the SUBSTITUTE function in a helper column or move to Power Query for more sophisticated transformations across many rows of data.
Wildcards combine well with Match Case for targeted cleanup. Searching for P* with Match Case unchecked matches anything starting with P or p. Adding Match Case restricts to only uppercase P. Adding Match Entire Cell makes the search find only cells whose entire contents start with that letter. Each option narrows the matching, and combining several lets you express surprisingly precise search patterns without ever needing real regex syntax for the everyday cleanup task you're working through right now.
Wildcard examples
Find What: *@gmail.com with Match Entire Cell Contents checked. Matches every cell whose entire content is an email address ending in @gmail.com. Replace With: empty (to clear those cells) or a tag value to mark them. The same pattern works for any domain — *@yahoo.com, *@company.com, etc. Useful for filtering or cleaning up email lists across rows in a contact spreadsheet.
Find and Replace with line breaks (Ctrl+J)
One of the lesser-known but most useful tricks: Ctrl+J in the Find what field (Windows) enters a line-break character. The field looks empty afterward because the line-feed character is invisible, but Excel knows you've entered it. Combined with Replace All, Ctrl+J lets you remove all line breaks across many cells at once or convert them to a different separator like comma-space. The reverse — putting Ctrl+J in Replace With and a comma in Find What — converts comma-separated values into multi-line cells.
Because the character is invisible, it's easy to think the keystroke didn't register. The cursor in the Find What field appears to be at the beginning of the field even though you've added a character. The fastest verification is to click Replace All and check the count of replacements — if Excel reports zero replacements where you expected many, the Ctrl+J probably didn't take. Try clicking back into Find What and pressing Ctrl+J again, then immediately click Replace All without clicking elsewhere.
The same Ctrl+J trick works for finding cells that contain line breaks anywhere in their text. Put Ctrl+J in Find What, leave Replace With empty, click Find All. Excel produces a list of every cell containing a line break. This is useful for auditing data quality before exporting to a downstream system that doesn't handle multi-line cells well, or for cleaning up imported data with unwanted line breaks from copy-paste operations or PDF extractions.
Beyond line breaks, you can search for other invisible characters using their character codes through SUBSTITUTE in a helper column. Excel's standard Find and Replace dialog doesn't have a way to type arbitrary character codes directly, but Ctrl+J handles the most common case (line feed, character 10). For carriage returns (character 13), tabs (character 9), or other control characters, a SUBSTITUTE formula in a helper column followed by paste-as-values into the original column is the cleanest workaround for that kind of cleanup.
For a fuller set of questions on this topic, see our excel latest version resource.
For a fuller set of questions on this topic, see our ms excel download free resource.
Replace All is reversible while the workbook is open: Ctrl+Z undoes the whole batch as one step. Once you close the workbook, the change is permanent. Always save a backup copy before running large Replace All operations on important data.
Searching within formulas vs values vs comments
The Look in dropdown (visible on the Find tab after you click Options) controls what part of a cell Excel searches. Formulas searches the underlying content: typed constants and the formula text itself. A cell containing =A1+B1 that displays 100 is found by searching for "A1+B1", but not by searching for "100". Values searches the displayed result, so the same cell is found by "100".
Formulas is essential when you need to find every cell that uses a specific function or references a specific cell. Search for "VLOOKUP" with Look in set to Formulas to find every VLOOKUP, or "B5" to find cells that reference B5, remembering this also matches B50, B52, and so on.
Notes and Comments search the text inside cell notes and threaded comments. Use them to audit annotations or find a specific tag; they are separate Look in choices, so switch the dropdown explicitly.
Be cautious with Replace All on formulas: the Replace tab works on formulas, so replacing text inside them can break them. Test with Replace (one at a time) before Replace All, and always save a backup.
Find and Replace — best-practice checklist
- ✓Save a backup copy before running large Replace All operations on important data.
- ✓Use Find Next once or twice to verify your search matches what you expect before Replace All.
- ✓Click the Options button to expose Match case, Match entire cell contents, and other advanced settings.
- ✓For pattern matching, use wildcards (*, ?) with the tilde (~) escape for literal asterisks or question marks.
- ✓Use Ctrl+J in Find What or Replace With to handle line-break characters.
- ✓Switch Look In to Formulas when searching for cells using a specific function or reference.
- ✓Limit scope by selecting a range first to keep the search targeted to what you intend to change.
- ✓Verify the replacement count Excel reports after Replace All to confirm coverage.
- ✓Use Ctrl+Z immediately if you notice an unexpected change before saving the workbook.
- ✓Consider Power Query or VBA for regex-style pattern transformations beyond Excel's wildcard support.
For repeated cleanup tasks, consider building a small VBA macro or a Power Query transformation that captures the find-replace logic as reusable code. A VBA macro can run a sequence of Find and Replace operations with one click, and Power Query can transform data on every refresh. Both approaches scale better than manually running the dialog dozens of times for the same cleanup. Power Query is the modern recommended path because it produces auditable, refreshable transformations rather than hidden macro logic that other users may not realize is in the workbook.
When Find and Replace isn't enough — alternatives
Excel's standard Find and Replace doesn't support full regex (regular expressions). For pattern-aware substitutions — finding phone numbers in any format and reformatting them, extracting numbers from mixed text, validating email patterns — you need a different tool. The three common alternatives are SUBSTITUTE in a formula, a VBA macro using VBScript regex, or Power Query with its Text functions.
SUBSTITUTE is the formula equivalent of Replace. =SUBSTITUTE(A1, "old", "new") replaces every occurrence of "old" with "new" in A1. Add a fourth argument to replace only a specific occurrence: =SUBSTITUTE(A1, "old", "new", 2) replaces only the second occurrence. SUBSTITUTE is useful when you need a non-destructive substitution (the original cell stays untouched) and when you want to chain multiple substitutions in a single formula across helper columns.
VBA macros can use the VBScript regex engine for pattern-aware substitution. A short macro reads each cell in a range, applies a regex pattern, and writes the result back. The pattern can include backreferences and replacement groups that the standard dialog can't handle. Most experienced Excel users keep a personal macro workbook with a few utility regex functions for repeated cleanup tasks across different data sets they encounter routinely.
Power Query is the modern recommended path. It supports Text functions such as Text.Replace, Text.Split, and Text.Contains. Build a transformation that takes your messy data through a series of cleanup steps. The transformation is auditable, refreshable, and reusable across multiple data refreshes. For any cleanup task that runs more than once on similar data structure, Power Query usually pays back the initial setup time many times over in saved hours.
Find and Replace — quick reference
Common Find and Replace use cases
Replace All with Match Case turned on lets you target specific case patterns. Replace "USA" with "USA" only when capitalization differs (e.g., "usa" → "USA"). Combine with Match Entire Cell for the most targeted replacement when only some cells need normalization while others are already correct in a column.
Find a specific character (space, comma, dollar sign) and Replace with empty string to strip it from text. Useful for cleaning numeric columns that imported with currency symbols or thousand separators that prevent Excel from recognizing them as numbers. Verify after the cleanup that the column shows right-aligned numbers rather than left-aligned text.
Replace All updates every instance of a deprecated product code, account number, or similar identifier across a workbook in seconds. Use Within: Workbook scope to catch instances on every sheet at once. Always save a backup first because Replace All on workbook scope is a powerful operation that touches every sheet immediately and may have unintended consequences.
Replace commas with line breaks (using Ctrl+J in Replace With) to convert comma-separated values into multi-line cells. Or the reverse — replace line breaks with commas to flatten multi-line cells into single-line CSV-friendly format. Apply Wrap Text after converting to multi-line so the breaks display visually in the affected cells.
Common Find and Replace mistakes
The most common mistake is forgetting Match case is off by default. A search for "USA" matches "usa", "Usa", and any other capitalization. If you wanted to replace only one specific case form, your Replace All probably caught more cells than you intended. Save backups before large Replace All operations and use Find Next once or twice to verify the matches before committing the bulk replace. Match Case off is the right default for most general searches but the wrong default for case-sensitive cleanup work.
The second-most-common mistake is forgetting that Find What treats your input as a substring search by default. "100" matches every cell containing 100 — including 1000, 100,000, $1,000,000.00, and so on. Add Match Entire Cell Contents to restrict to cells whose entire content is exactly 100. This is the option that catches most over-matching surprises when running Replace All on numeric or short-text columns where partial matches would be unintended.
The third issue is wildcards behaving unexpectedly when you're searching for literal asterisks or question marks. Excel treats 5*3 as a wildcard pattern that matches anything starting with 5 and ending with 3. To search for the literal string "5*3", escape the asterisk with tilde: 5~*3. Same for question marks. The tilde escape is one of the most-forgotten features of the dialog because most searches don't involve literal asterisks or question marks needing escape treatment.
Finally, Find and Replace remembers your last settings, such as Look in, Within, and Match case. If a search unexpectedly finds nothing even though the value is visible, open Options and check Look in: with Formulas selected, a number shown by a formula will not match its displayed value.
Sample Excel Practice Questions
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
Which keyboard shortcut opens the Find and Replace dialog in Excel?
- A. Ctrl+H
- B. Ctrl+F
- C. Ctrl+G
- D. Ctrl+R
Answer: A. Ctrl+H
Ctrl+H opens the Find and Replace dialog directly on the Replace tab, while Ctrl+F opens it on the Find tab.
In Excel, which ribbon tab contains the Get & Transform Data group used to launch Power Query?
- A. Home
- B. Data
- C. Formulas
- D. Insert
Answer: B. Data
Power Query connectors live in the Get & Transform Data group on the Data tab.
Which Excel tool is used to find the input value needed to achieve a specific formula result?
- A. Solver
- B. Goal Seek
- C. Scenario Manager
- D. Data Table
Answer: B. Goal Seek
Goal Seek finds the required input value by working backward from a desired formula result.
Which language does Power Query use to record the steps you apply in the Power Query Editor?
- A. DAX
- B. VBA
- C. M
- D. SQL
Answer: C. M
Every applied step is written in the M formula language, visible in the Advanced Editor.
Excel Questions and Answers
About the Author
Business Consultant & Professional Certification Advisor
Wharton School, University of PennsylvaniaKatherine Lee earned her MBA from the Wharton School at the University of Pennsylvania and holds CPA, PHR, and PMP certifications. With a background spanning corporate finance, human resources, and project management, she has coached professionals preparing for CPA, CMA, PHR/SPHR, PMP, and financial services licensing exams.