Find and Replace in Excel: Wildcards, Tricks, and Power Workflows

Find and Replace in Excel is one of those everyday tools that most users learn at the surface level and stop there. The basic Ctrl+F and Ctrl+H shortcuts handle simple text searches, but the dialog hides several powerful features — wildcards, match-case, match-entire-cell, search by row or column, search across the whole workbook, search within formulas vs values vs comments — that turn it into a cleanup powerhouse for anyone working with messy data.

This guide covers Find and Replace from the basics through the advanced. We'll walk through the keyboard shortcuts, the Options pane that exposes the deeper features, the wildcard syntax (asterisk, question mark, tilde escape), how to search and replace line breaks and other invisible characters, the difference between Replace and Replace All, and the workarounds when Excel's built-in tool isn't quite enough — using SUBSTITUTE in formulas, VBA macros, or Power Query for regex-style transformations that the standard dialog doesn't support directly.

The single 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, the wildcard feature lets you find and replace patterns that simple literal text searches can't catch. Most users miss this for years before discovering it.

The shortcut keys are Ctrl+F (Cmd+F on Mac) for Find and Ctrl+H (Cmd+H on Mac) for Replace. Both open the same dialog, just on different tabs. Click the Options button to expose the deeper features. The dialog is mostly the same across Excel versions — Excel 365, Excel 2019, Excel 2021, and Excel for the Web all share the core layout, with minor cosmetic differences. Excel for the Web is slightly more limited in its support for some advanced options.

For users coming from text editors with regex support (Notepad++, VS Code, Sublime), Excel's Find and Replace can feel limiting. Excel doesn't support full regular expressions in the standard dialog. The asterisk and question mark wildcards are the closest equivalent, and they handle most everyday cleanup needs. For real regex transformations on Excel data, the right tool is usually Power Query or a short VBA macro, both of which we cover later in this guide for power users who need pattern matching beyond what wildcards offer.

The basics — Ctrl+F and Ctrl+H

Press Ctrl+F to open the Find dialog. Type the text you want to find and click Find Next or Find All. Excel highlights matches in the worksheet. Find Next steps through matches one at a time; Find All produces a list at the bottom of the dialog showing every match with its cell reference. Click any item in the Find All list to jump directly to that cell. The Find tab is also useful for navigation — many power users use Ctrl+F to jump around large worksheets faster than scrolling.

Press Ctrl+H to open the Replace tab of the same dialog. Type the text to find in Find what and the replacement text in Replace with. Click Find Next to step through matches one at a time and Replace to replace the current match. Click Replace All to replace every match at once. Excel reports the number of replacements made when you use Replace All. The result message is the easiest way to verify your 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, including the option to search the entire workbook, search by rows or columns, search within values or formulas or comments, and toggle Match Case and Match Entire Cell Contents. The Options pane stays expanded between sessions, so you only need to expand it once to see the full feature set going forward in your installed copy of Excel.

Selection matters. If you have a multi-cell range selected when you open Find and Replace, the search runs only within that selection. If you have a single cell selected, the search runs across the entire active sheet (or workbook, if Within is set to Workbook). This selection-scoped behavior is useful for limiting the scope of bulk replaces — select the column or table you want to clean up, then run Replace All without affecting unrelated cells elsewhere in the workbook.

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.

Find and Replace with line breaks (Ctrl+J)

One of the lesser-known but most useful tricks: Ctrl+J in the Find What field 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.

Searching within formulas vs values vs comments

The Look In dropdown lets you choose what part of a cell Excel searches. Values (the default) searches the displayed text — what you see in the cell after formula calculation. A cell containing the formula =A1+B1 that displays 100 would be found by searching for "100" with Look In set to Values. Formulas searches the formula text itself — that same cell would be found by searching for "A1+B1" or "+B1" with Look In set to Formulas.

The Formulas option is essential when you need to find cells that reference a specific function or specific input cell. Want to find every cell using VLOOKUP? Search for "VLOOKUP" with Look In set to Formulas. Want to find every cell that references cell B5? Search for "B5" with Look In set to Formulas — being aware that this will also match cells like B50, B52, etc., unless you use exact-cell-reference patterns or further filtering.

Comments (now called Notes in newer Excel) searches the text inside cell comments. Useful for auditing notes attached to cells, finding all cells with a specific tag in their notes, or cleaning up automatically generated comments. The Comments search is separate from cell-text searches; you must explicitly switch the Look In dropdown to Comments rather than relying on a single search across all content types in the worksheet.

One quirk: switching Look In affects both Find and Replace. Be cautious when running Replace All with Look In set to Formulas — replacing text inside formulas can break them. Test with Replace (one at a time) before using Replace All for formula edits, and always save a backup. For large-scale formula edits, the better approach is usually a search-and-replace at the file level using a tool that understands the XLSX XML structure, but that's an advanced technique most users don't need.

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 the Text functions and M-language regex support that handle larger-scale pattern work.

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 (Text.Replace, Text.Split, Text.Contains, etc.) and M-language patterns. 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.

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, the Look In setting is sticky between sessions. If you previously searched in Formulas and forgot to switch back to Values, your next search may seem to find no matches even though the value is clearly visible in the worksheet. Always glance at the Look In dropdown when starting a search if you've used Find and Replace recently with non-default settings on the same Excel installation across recent days of use.