Concept
1. Shortcuts
- Ctrl + F — Find
- Ctrl + H — Replace
Click Options >> to see the settings. Select a range first if you want to change only that range; otherwise Excel works on the whole sheet.
2. The three wildcards
| Wildcard | Means | Example | Matches |
|---|---|---|---|
* |
any number of characters | Sh*a |
Sharma, Shama, Shikha |
? |
exactly one character | P10? |
P100 … P109 and P10A (whole-cell match) |
~ |
"treat the next character literally" | ~* |
an actual * |
For exact four-character codes, enable Match entire cell contents; otherwise P10? can also match part of P1010. ? matches any character, not just a digit, so inspect codes such as P10A. Keep this option off for removing a suffix within a name. See Microsoft’s Find and Replace guidance.
3. Useful clean-ups
Remove text in brackets: Rahul Sharma (Delhi) → Rahul Sharma
Find: (*) (with a space before the bracket) → Replace with: (leave empty) → Replace All.
Remove everything after a dash: INV-2026-0457 → INV
Find: -* → Replace with: (empty).
Remove a company suffix: Find * Pvt Ltd would delete the whole name — careful. Use Pvt Ltd (no wildcard) to remove just the suffix.
Remove real asterisks: Price* → Price
Find: ~* → Replace with: (empty). Without the ~, Find * → Replace with empty would delete every cell's content.
Replace one single character: codes A1B, A2B, A9B → all A-B
Find: A?B → Replace with: A-B.
4. Options that prevent accidents
| Option | What it does |
|---|---|
| Match case | Delhi won't match DELHI |
| Match entire cell contents | Find Ram matches only cells that are exactly "Ram", not "Ramesh" or "Shri Ram" |
| Within: Sheet / Workbook | search one sheet or all sheets |
| Look in: Formulas / Values | search inside formulas, or in displayed results |
Example: replacing the city Ram Nagar → Ramnagar without touching a person named Ram Kumar? Turn on Match entire cell contents.
5. Remove line breaks (Alt + Enter)
Addresses copied from forms often have line breaks inside the cell. In the Find box press Ctrl + J (nothing visible appears, but a line break is entered). Replace with a space → Replace All.
6. Find All — a quick report
Click Find All instead of Find Next. The list shows every matching cell. Press Ctrl + A in that list to select all those cells on the sheet — then you can colour them or delete them together.
7. Safety first
Replace All can't be undone after you save and close. Use Find All first to see how many cells will change, and keep a copy of the sheet.
Common mistakes
Using * alone and wiping the data. Forgetting ~ when searching for a real * or ?. Not selecting a range, so the whole sheet (including headers or formulas) changes. Leaving Match entire cell contents on from a previous search.