Excel’s Find and Replace tool can fail to locate text you know is present. This often happens when hidden formatting characters or special search options are active. The tool interprets characters like asterisks, question marks, and line breaks as search commands. This article explains how to adjust your search settings to find the exact text.
Key Takeaways: Fixing Find and Replace
- Options > Match entire cell contents: Uncheck this box to search for text within a cell, not just the whole cell value.
- Options > Use wildcards: Uncheck this box to search for literal asterisks (*) and question marks (?), not as wildcard characters.
- Find what: ~* or ~?: Use the tilde (~) before a wildcard character to search for it literally when the Use wildcards option is checked.
Why Find and Replace Fails to Locate Text
Excel’s Find and Replace dialog has several options that change how your search term is interpreted. The most common cause of failed searches is the Use wildcards option. When enabled, characters like the asterisk (*) and question mark (?) are not treated as normal text. An asterisk represents any number of characters, and a question mark represents any single character. If you are searching for a part number like “ACME-12*”, Excel will look for cells starting with “ACME-12” followed by any characters, which may not be what you intend.
Another frequent issue is searching for text that contains a line break. A line break within a cell is a non-printing character. The standard Find dialog does not show it, so your search for “FirstLineSecondLine” will fail because the invisible line break character is between the words. You need a specific method to search for these characters. The Match entire cell contents option is also a common blocker. If this is checked, Excel will only return a match if the cell’s entire content is identical to your Find what string.
How Hidden Formatting Affects Searches
Cells can contain non-printing characters beyond line breaks. These include spaces, tabs, and non-breaking spaces often copied from web pages or other applications. Multiple regular spaces at the start or end of your search term can cause a mismatch. Excel is precise; searching for “Data” will not find ” Data ” with leading or trailing spaces unless you include those spaces in your search box.
Steps to Correct Your Find and Replace Search
- Open the Find and Replace dialog
Press Ctrl + F on your keyboard. Alternatively, go to the Home tab on the ribbon, click Find & Select in the Editing group, and choose Find. - Expand the search options
In the Find and Replace dialog box, click the Options button. This reveals the advanced search settings that are likely causing the issue. - Disable the Use wildcards option
Look for the checkbox labeled Use wildcards. If it is checked, click it to uncheck it. This ensures characters like * and ? are searched for as literal text. - Disable the Match entire cell contents option
Find the checkbox for Match entire cell contents. If it is checked, uncheck it. This allows you to find text that is part of a cell’s content. - Clear the Format settings
Click the Format button next to the Find what box. If it says “Preview of the format to find,” click the drop-down arrow on the Format button and choose Clear Find Format. A previously set format can restrict the search. - Perform a new search
Type your text into the Find what box again and click Find All. Your search should now return the expected matches.
Searching for Special Characters Like Line Breaks
To find a line break character, you must enter a specific code in the Find what box.
- Place cursor in the Find what box
Click inside the Find what field in the Find and Replace dialog. - Insert the line break code
Press Ctrl + J on your keyboard. You will not see a character appear, but the cursor may move slightly. - Run the search
Click Find All. Excel will now locate all cells containing a line break at the position you specified.
If Find and Replace Still Shows No Matches
Search Scope is Set to a Single Worksheet
By default, Find searches only the active worksheet. If your data is on another sheet, it will not be found. In the Find and Replace dialog, under the Within option, select Workbook instead of Sheet. This expands the search to all sheets in your file.
Look In is Set to Formulas or Values
The Look in dropdown has three settings: Formulas, Values, and Comments. If a cell displays text as a result of a formula, searching in Formulas may not find it. Change the Look in setting to Values to search the displayed results of cells. If the text is in a comment, select Comments.
Searching for Numbers Stored as Text
A cell may display a number like 100, but it might be stored as the text string “100”. If you search for the number 100 with default settings, Excel may not find the text version. To be sure, try searching for the number with the Match entire cell contents option unchecked. Alternatively, use the error checking green triangle or the VALUE function to convert text to numbers first.
Find and Replace Option Settings Compared
| Item | Option Enabled (Checked) | Option Disabled (Unchecked) |
|---|---|---|
| Use wildcards | * matches any sequence, ? matches any single character | * and ? are treated as literal asterisk and question mark characters |
| Match entire cell contents | Only finds cells where the content exactly equals the Find what string | Finds the text string anywhere inside a cell’s content |
| Match case | Searches are case-sensitive (“Data” ≠ “data”) | Searches are not case-sensitive |
| Look in | Formulas: Searches cell formulas Values: Searches displayed results Comments: Searches comment text only |
N/A – this is a dropdown selection, not a checkbox |
You can now successfully use Find and Replace by managing the Use wildcards and Match entire cell contents options. For advanced searches, remember the Ctrl + J shortcut to find line breaks. Next, try using the Replace tab with these settings to batch-edit data, such as removing extra spaces by searching for two spaces and replacing with one.