Part of the free Module 3: Find and Replace · Lesson 2 of 6 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.
Excel Find and Replace wildcards let one search match many different values. The asterisk * stands for any number of characters, the question mark ? for exactly one, and the tilde ~ turns a wildcard back into a literal character. They work only in the Find what box; anything typed in Replace with is inserted exactly as written.
How the three wildcards work
| Wildcard | Meaning | Find what | Matches | Does not match |
|---|---|---|---|---|
* |
Any number of characters, including none | Man* |
Manager, Manchester, Man | Human (no partial-word rule, but Match entire cell contents would reject it) |
? |
Exactly one character | PK-?? |
PK-01, PK-AB | PK-1, PK-100 |
~ |
Treat the next * ? or ~ as a literal | ~*Total |
*Total | Grand Total |
Two rules explain most surprises. First, a wildcard search is a “contains” search unless Match entire cell contents is ticked, so Man* also finds “Sales Manager”. With the box ticked, the cell must match the pattern from its first character to its last. Second, Replace with is literal. Typing * in Replace with does not keep the matched text; it inserts an asterisk. To keep part of a cell you must match only the part you want to remove.
Pattern 1: remove everything after a character
Find what ,*, Replace with empty. “Delhi, India” becomes “Delhi”. The comma and every character after it are matched and deleted. Use -* for “Name – ID” strings or (* to cut from an opening bracket.
Pattern 2: remove everything before a character
Find what *,, Replace with empty. “Delhi, India” becomes ” India” (note the leading space). Follow it with a second replace of a single space with nothing, or use *, including the space in the first pattern.
Pattern 3: delete text inside brackets
Find what (*), Replace with empty. “Sales (Q1 provisional)” becomes “Sales “. Because * is greedy it matches from the first opening bracket to the last closing bracket in the cell, so “A (1) B (2)” becomes “A “. Handle multiple bracket pairs one at a time with Replace rather than Replace All.
Pattern 4: strip a fixed-length code with ?
Find what ???-, Replace with empty, Match entire cell contents off. “IND-Mumbai” becomes “Mumbai” and “USA-Boston” becomes “Boston”, but a five-letter code such as “GBRUK-London” is left alone because ?, unlike *, demands exactly three characters before the hyphen.
Pattern 5: keep only the email domain or user name
Find what *@ and Replace with empty keeps the domain: “pk@example.com” becomes “example.com”. Find what @* keeps the user name instead. Run one or the other, not both.
Pattern 6: find a literal asterisk, question mark or tilde
Imported price lists often mark items with * or ?. Find what ~* finds the asterisk itself, ~? finds a question mark and ~~ finds a tilde. Replace with can hold a plain * because that box is always literal. To delete a trailing asterisk from “Premium*”, Find what ~*, Replace with empty.
Pattern 7: remove or insert line breaks with Ctrl+J
- Select the range and press Ctrl+H.
- Click in Find what and press Ctrl+J. The box looks empty, but a tiny blinking dot shows the line-feed character is there.
- Type a space (or a comma and a space) in Replace with.
- Click Replace All. Multi-line addresses collapse into one line.
The reverse also works: put , in Find what and press Ctrl+J in Replace with to split a comma list onto separate lines. Turn on Wrap Text to see the result. Clear the Find what box afterwards by selecting it and pressing Delete, because the invisible character stays in the history.
Pattern 8: collapse double spaces
Find what two spaces, Replace with one space, and click Replace All until Excel reports zero replacements (three spaces need two passes). For a whole column the TRIM function does the same in one formula and also removes leading and trailing spaces.
Pattern 9: remove non-breaking spaces from web data
Text copied from web pages contains character 160, which looks like a space but TRIM ignores. In a spare cell enter =CHAR(160), copy the result, paste it into Find what (or hold Alt and type 0160 on the numeric keypad), leave Replace with empty and Replace All. Numbers that refused to add up now calculate.
Pattern 10: replace whole values only
Find what N/A with Match entire cell contents ticked, Replace with empty. Only cells that contain exactly N/A are cleared; “N/A – pending” stays. The same setting turns a wildcard into an exact-shape test: ??-???? with the box ticked finds only two-letter, hyphen, four-character codes.
All ten patterns at a glance
| # | Goal | Find what | Replace with | Result |
|---|---|---|---|---|
| 1 | Remove text after a comma | ,* |
(empty) | Delhi, India becomes Delhi |
| 2 | Remove text before a comma | *, |
(empty) | Delhi, India becomes India |
| 3 | Delete bracketed text | (*) |
(empty) | Sales (Q1) becomes Sales |
| 4 | Strip a 3-letter prefix | ???- |
(empty) | IND-Mumbai becomes Mumbai |
| 5 | Keep email domain | *@ |
(empty) | pk@example.com becomes example.com |
| 6 | Delete a literal asterisk | ~* |
(empty) | Premium* becomes Premium |
| 7 | Remove line breaks | Ctrl+J | space | Two lines become one |
| 8 | Collapse double spaces | two spaces | one space | Repeat until 0 replacements |
| 9 | Remove non-breaking spaces | Alt+0160 | (empty) | Text numbers become numbers |
| 10 | Clear exact N/A cells | N/A + entire cell |
(empty) | Only whole-cell matches cleared |
Worked example: clean a product export
| Before | Pattern | After |
|---|---|---|
| Laptop (Refurbished) – North | 1 then 3 | Laptop |
| Monitor 27in* – South | 1 then 6 | Monitor 27in |
| IND-Keyboard – East | 1 then 4 | Keyboard |
- Select A2:A4 and press Ctrl+H. Find what
-*, Replace with empty, Replace All: 3 replacements. The region suffixes are gone. - Find what
(*)(with the leading space), Replace All: 1 replacement, leaving “Laptop”. - Find what
~*, Replace All: 1 replacement, leaving “Monitor 27in”. - Find what
???-, Replace All: 1 replacement, leaving “Keyboard”.
Four Replace All passes, no formulas, and the column is ready for a pivot table.
Tips and common mistakes
- Test with Find All first. The results list shows exactly which cells a pattern touches before you commit.
- The asterisk is greedy.
(*)runs from the first bracket to the last bracket in the cell, not to the nearest one. - Replace with never uses wildcards. If you need to keep the matched text and add to it, use a formula such as SUBSTITUTE or Flash Fill instead.
- Numbers become text. Stripping “kg” from “12 kg” leaves the text “12”. Convert with Text to Columns or multiply by 1 with Paste Special.
- Select the range first. With one cell selected, Replace All runs across the whole sheet, including headers and formulas.
- Clear Ctrl+J from the history. The invisible line feed stays in Find what and makes the next search fail.
- Use Match case when a pattern such as
ID*must not catch “Video Editor”.
Errors and how to fix them
| Symptom | Cause | Fix |
|---|---|---|
| “We couldn’t find what you were looking for” | Match entire cell contents or a Format filter is still on from an earlier search | Options, untick the box, Format > Clear Find Format |
| Whole cell disappeared | The pattern started or ended with * and matched everything | Ctrl+Z; anchor the pattern to a separator such as a comma or hyphen |
| Asterisk was not found | Excel read * as a wildcard | Search for ~* |
| Totals wrong after cleaning | The cleaned numbers are stored as text | Select the column, Data > Text to Columns > Finish |
| Line breaks still present | Ctrl+J was pressed while the Find what box was not focused | Click inside Find what first, then press Ctrl+J |
Practice exercise
- In the practice file add a column E with
=B2&" ("&C2&")", paste as values, then remove the bracketed designation with pattern 3. - Create a column of email addresses from the names, then keep only the domain with pattern 5.
- Type three-line addresses in a column using Alt+Enter, then join them into one line with Ctrl+J (pattern 7).
- Add a trailing * to three names and remove it with
~*. - Enter “12 kg”, “7 kg”, “30 kg”, strip the unit, and convert the results to numbers so SUM works.
Key takeaways
- * matches any run of characters, ? matches exactly one, ~ escapes a wildcard.
- Wildcards work in Find what only; Replace with is always literal.
- Anchor patterns to a separator so a greedy * removes only what you intend.
- Ctrl+J in Find what or Replace with handles line breaks.
- Match entire cell contents turns a pattern into an exact-shape test.
- Check the results with Find All and keep Ctrl+Z ready.
Related lessons
- Find and Replace in Excel: the complete guide (module hub)
- Go To Special: select blanks, formulas and visible cells
- Replace inside formulas and sheet names safely
- Find and replace with SUBSTITUTE, REPLACE and VBA
- Excel SUBSTITUTE function
- Excel TRIM function
- Flash Fill: extract and combine text by pattern
- Microsoft Support: Find or replace text and numbers on a worksheet
Frequently asked questions
How do I remove everything after a comma in Excel?
Select the cells, press Ctrl+H, type a comma followed by an asterisk (,*) in Find what, leave Replace with empty and click Replace All. The asterisk matches every character after the comma, so only the text before it remains. Use the same idea with a hyphen, a space or a bracket.
How do I find a literal asterisk or question mark in Excel?
Put a tilde in front of it. Search for ~* to find an asterisk, ~? to find a question mark and ~~ to find a tilde. The tilde tells Excel to treat the next character as ordinary text. The same escape works in COUNTIF, SUMIF, MATCH and other functions that accept wildcards.
How do I remove line breaks in Excel cells?
Press Ctrl+H, click in the Find what box and press Ctrl+J, which enters the invisible line-feed character. Type a space in Replace with and click Replace All. Every Alt+Enter break in the selection becomes a space. To do it with a formula use SUBSTITUTE(A2,CHAR(10),” “).
Can I use regular expressions in Excel Find and Replace?
No. The dialog supports only *, ? and ~. Excel 365 has the REGEXREPLACE, REGEXEXTRACT and REGEXTEST functions for pattern work in formulas, and VBA can use the VBScript RegExp object. In Excel 2021 and earlier, combine SUBSTITUTE, LEFT, MID and FIND, or use Flash Fill.
Why did the wildcard replace delete the whole cell?
A pattern that begins and ends with an asterisk, such as *Total*, matches the entire cell, so replacing it with nothing empties the cell. Anchor the pattern to a separator, for example ” – *”, so the asterisk only reaches from that separator to the end. Undo with Ctrl+Z.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.