To find something in an Excel worksheet, press Ctrl+F on Windows (Command+F on Mac), type the text or value, and choose Find Next; use the search options to look across a whole workbook or match exact cell contents. Excel also has a worksheet function named SEARCH, which returns the position of text inside another text string. The steps below explain both, so you can choose the right kind of search for the task.
What You’ll Need
- An Excel workbook containing the data you want to search. These steps use Excel for Microsoft 365 on Windows.
- If you want to use the SEARCH formula, a cell containing the text to examine and a cell or phrase containing the text you want to find.
- On a Mac, use Command+F instead of Ctrl+F. Excel for the web has a Find feature too, though its layout can differ slightly from the desktop app.
Step-by-Step
- Decide which kind of search you need. Use Excel’s Find tool when you want to locate a cell in a worksheet or workbook, then jump to it. Use the SEARCH function in a formula when you want Excel to report where a piece of text occurs inside a cell, or use that result in another calculation. For example, Find can take you to a cell containing a customer name; SEARCH can tell you whether a note contains a word such as “urgent.”
- Open Find. Select a worksheet, then press Ctrl+F on Windows or Command+F on Mac. Alternatively, on Windows select Home > Find & Select > Find. Type the word, number, or phrase into the Find what box. Select Find Next to go to the next matching cell. Excel selects the cell containing the match; the text does not have to be at the beginning of the cell.
- Search the whole workbook if necessary. In the Find dialog, select Options to show more settings. The Within menu lets you choose Sheet or Workbook. Sheet searches only the active worksheet; Workbook searches across the workbook’s worksheets. Choose Workbook when you know the information is somewhere in the file but are not sure which tab contains it. If a search seems to miss a match, confirm that the correct scope is selected.
- Choose what Excel searches. In the expanded options, use the Look in menu to search formulas, values, or notes, depending on the choices available in your Excel version. Choose Values to search what cells display, such as a calculated result. Choose Formulas when you need to find text that appears in the formula itself. For instance, a cell may display 250 while its formula contains a reference to another sheet; searching for 250 and searching for the formula text are different tasks.
- Set matching rules when needed. Select Match case if uppercase and lowercase letters must be treated differently. Select Match entire cell contents if Excel should find only cells whose full contents equal your search text. Leave that option cleared to find a word or number even when it is part of a longer cell entry. These settings are useful for distinguishing an exact code from a longer code that includes the same characters.
- Move through the matches. Select Find Next repeatedly to visit each result. Find All displays a list of matching cells in the dialog; selecting a result takes you to that cell. Excel can wrap around to earlier results after reaching the end of the search area, so check the selected cell and address rather than assuming each Find Next result is new. To start over with another term, replace the text in Find what.
- Use the SEARCH function in a cell. Suppose A2 contains the text West Oak Lane. With the text to examine in A2, enter
=SEARCH("oak",A2)in another cell. The result is 6, the position where “oak” begins. SEARCH is not case-sensitive, so it finds “Oak” even though the formula uses lowercase letters. The result is a number, which means it can also be used in other formulas or tests. - Set a starting position when useful. SEARCH can start looking after the beginning of the text. With A2 containing West Oak Lane, enter
=SEARCH("Lane",A2,7). The result is 10 because Excel begins looking at character 7 and finds “Lane” at character 10. The optional third argument is the starting character position; use 1, or omit it, to search from the start. A start number larger than the text length can produce an error. - Test whether a cell contains a word. SEARCH returns a number when it finds a match and an error when it does not. To produce a simple TRUE or FALSE result, put the text to check in B2. With B2 containing Late delivery, enter
=ISNUMBER(SEARCH("late",B2)). The result is TRUE. If B2 contains On schedule, the result is FALSE. This is useful for checking text without manually opening Find for each cell. - Use wildcards for flexible text searches. In the Find dialog, an asterisk (*) can stand for any number of characters and a question mark (?) can stand for one character. For example, searching for Jo*n can match “John” or “Joan.” SEARCH also recognizes * and ? in its find_text argument. To search for an actual asterisk or question mark rather than use it as a wildcard, put a tilde (~) before it. Use wildcards carefully: a broad pattern can return many results that are not the specific entry you intended.
Common Mistakes
- Confusing Find with SEARCH. Find is a navigation tool that takes you to matching cells. SEARCH is a formula that returns a character position. If you want to locate a cell, use Ctrl+F; if you want a formula result, use SEARCH.
- Searching the wrong area. Find may be limited to the active sheet when you expected the whole workbook. Check the Within setting and whether the right sheet is active.
- Expecting SEARCH to return the found word. SEARCH returns a number, not the matching text. For example, a result of 6 means the match starts at character 6. It does not extract the word or change the cell.
- Expecting case-sensitive results from SEARCH. SEARCH ignores letter case. If case matters in a formula, use FIND instead; FIND is case-sensitive. The Find dialog has its own Match case setting.
- Searching for displayed numbers without checking formats. A date or number may be shown in a formatted way that differs from its underlying value. Search for the visible text, and if it is not found, check Look in and the cell’s number format.
Tips
- Use a distinctive fragment rather than a common word. A unique surname, order number, or phrase can reduce the number of Find Next results.
- Clear Match entire cell contents when looking for a fragment inside longer entries. Turn it on when the whole cell must equal the search term.
- Use Find All when you want to review several matches at once, especially in a large sheet. The result list can also help confirm which worksheet contains a match.
- SEARCH ignores case but still requires the characters to occur in the right order. If the text may contain extra spaces, clean the source data or search for a shorter, reliable fragment.
- When working with formulas, keep the search text in a cell rather than typing it directly into every formula. For example, if C1 contains late and the text to check is in B2, use
=ISNUMBER(SEARCH(C1,B2)). Changing C1 then updates the test without editing the formula.
Troubleshooting
Find says there are no matches. Check spelling, spaces, punctuation, and the Within setting. Clear Match entire cell contents to allow a partial match, and check whether Match case is enabled. If the target is a calculated result, set Look in to Values; if you are looking for part of a formula, choose Formulas. Also confirm that the intended workbook and worksheet are open.
SEARCH returns #VALUE!. This normally means the specified text was not found in the cell. Check for typos or hidden spaces and make sure the cell reference points to the text you intended. If “not found” is an expected outcome, you can handle the error with IFERROR. With the text to search in B2, for example, =IFERROR(SEARCH("late",B2),"Not found") returns the position when “late” appears and Not found otherwise.
The formula returns an unexpected position. SEARCH counts every character, including spaces and punctuation, starting at 1. Check the exact contents of the cell and the optional start_num argument. If you supplied a starting position, make sure it is a positive number no greater than the text length. If the formula displays as text instead of calculating, check that the cell is not formatted as Text; change it to General, then re-enter the formula.
The Find dialog looks different or the shortcut does not open it. On Windows, open it from Home > Find & Select > Find. On Mac, use Command+F or the app’s Find command. In Excel for the web, Ctrl+F generally opens the Find pane, but browser and app behavior can vary; use the Home tab’s Find option if needed.
Frequently Asked Questions
Is SEARCH the same as Find in Excel?
No. Find locates matching cells and lets you navigate to them. SEARCH is a worksheet function that returns the position of one text string inside another.
Does Excel’s SEARCH function distinguish uppercase and lowercase?
No. SEARCH is case-insensitive, so it treats “Oak” and “oak” as a match. Use FIND for a case-sensitive formula search, or enable Match case in the Find dialog.
How do I search every worksheet in a workbook?
Open Find, select Options, and set Within to Workbook. Then use Find Next or Find All to review matches across the workbook rather than only on the active sheet.
Can I search for part of a cell’s contents?
Yes. In the Find dialog, leave Match entire cell contents cleared. SEARCH also finds a partial string and returns the character position where that string starts.
Can SEARCH find a word and return the word itself?
SEARCH only returns a position number. Use that position in another formula if you need to extract text, or use Find to navigate to the cell containing it.
What does #VALUE! mean in a SEARCH formula?
Usually, the formula did not find the requested text in the cell. Check the search text and cell reference, or wrap the formula in IFERROR if you want a custom result when there is no match.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
- Excel Formula Opposite of CONCATENATE: Split Text ApartThe closest Excel formula to the opposite of CONCATENATE is =TEXTSPLIT(A2,"|"): it takes text in A2 and splits…
- XLOOKUP Excel Version: Compatibility and AvailabilityXLOOKUP is available in Microsoft 365, Excel 2021, and Excel 2024, as well as Excel for the web…
- Excel NPV With Different Rates: Formula, Steps, ExampleExcel’s NPV function accepts one discount rate, so it cannot directly calculate net present value when the rate…
- Excel LEN Function: Count Text Characters in CellsIn Excel for Microsoft 365, use the LEN function to count the characters in a cell: with text…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.