=SEARCH("apple",A2) returns the position where apple starts in A2, counting from 1, and ignores upper and lower case. =FIND("apple",A2) does the same but only matches the exact case. When the text is not there, both return #VALUE!.
| A | B | C | |
|---|---|---|---|
| 1 | Text | FIND("apple") | SEARCH("apple") |
| 2 | Apple pie | #VALUE! | 1 |
| 3 | Green apple | 7 | 7 |
| 4 | Banana bread | #VALUE! | #VALUE! |
#VALUE! An argument has the wrong type, such as text where a number belongs.Apple pie starts with a capital A, so FIND does not match and returns #VALUE!, while SEARCH returns 1. In Green apple both return 7: the space counts as a character. Banana bread has no apple at all.
Syntax
=FIND(find_text, within_text, [start_num])
=SEARCH(find_text, within_text, [start_num])
find_text: what to look for. One character or a word.within_text: the cell to look in.start_num: the position to start from, 1 if left out. The result still counts from the first character of the cell.
If cell contains text
The position itself is rarely what you want. Usually the question is "does this cell contain the word?". A position is a number and a miss is an error, so ISNUMBER turns the result into TRUE or FALSE:
| A | B | C | |
|---|---|---|---|
| 1 | Note | Contains "late" | Status |
| 2 | Arrived late | TRUE | Check |
| 3 | On time | FALSE | OK |
| 4 | LATE by 2 days | TRUE | Check |
| 5 | Delivered | FALSE | OK |
SEARCH matches LATE in A4 because it ignores case. Note that it would also match latest or chocolate: SEARCH looks for characters, not whole words. Searching for " late" with a leading space narrows it a little. To count how many cells contain a word, =COUNTIF(A2:A5,"*late*") is shorter; see counting cells with text.
| A | B | |
|---|---|---|
| 1 | Subject | Urgent? |
| 2 | URGENT: server down | |
| 3 | Monthly report | |
| 4 | Refund request, urgent |
Your turn: In B2, return TRUE if the subject in A2 contains the word urgent in any capitalisation, and FALSE if not. The formula fills down to B4.
Find the second occurrence
start_num lets you start looking after the first match. Put one FIND inside another: the inner one finds the first dash, and the outer one starts one character later.
| A | B | C | |
|---|---|---|---|
| 1 | Code | First dash | Second dash |
| 2 | NYC-2041-B | 4 | 9 |
| 3 | BOSTON-15-AA | 7 | 10 |
With the two positions, MID can take what is between the dashes: =MID(A2,B2+1,C2-B2-1) returns 2041.
| A | B | |
|---|---|---|
| 1 | Position | |
| 2 | ana@coddy.tech |
Your turn: In B2, return the position of the @ in the email address.
FIND and SEARCH return #VALUE!
#VALUE! here only means "not found". Replace it with something useful:
| A | B | |
|---|---|---|
| 1 | Position of @ | |
| 2 | ana@coddy.tech | 4 |
| 3 | not an email | 0 |
Do not compare the raw result with a number, as in =FIND("@",A2)>0: a miss is an error, and the comparison returns #VALUE! instead of FALSE. Test it with ISNUMBER, as in the section above.
Wildcards: only SEARCH
SEARCH accepts the wildcards ? (any one character) and * (any run of characters). FIND treats them as the literal characters. In Excel:
=SEARCH("b?d","a bad day") returns 3
=SEARCH("2*B","NYC-2041-B") returns 5
=SEARCH("~?","Why?") returns 4, the tilde means a real question mark
=FIND("?","Why?") returns 4, FIND has no wildcards
More on *, ? and ~ across Excel functions is on the wildcards page.
FIND vs SEARCH vs Ctrl+F
| FIND | SEARCH | Ctrl+F | |
|---|---|---|---|
| Case | Must match | Ignored | Ignored, unless Match case is ticked |
| Wildcards | No | ?, *, ~ | ?, *, ~ |
| Not found | #VALUE! | #VALUE! | A message |
| Result | A position, recalculated | A position, recalculated | Selects the cell |
To locate a value once, Home > Find & Select > Find (Ctrl+F, Cmd+F on a Mac) is faster than a formula. To flag or extract in every row, and keep it updated, use FIND or SEARCH. Pick FIND when case carries meaning (product codes like ab and AB), SEARCH for everything people type.
Frequently Asked Questions
What is the difference between FIND and SEARCH in Excel?
FIND is case-sensitive and does not accept wildcards. SEARCH ignores case and accepts ? and *. =FIND("a","Apple") is #VALUE!, while =SEARCH("a","Apple") is 1.
How do I extract the text between two dashes in Excel?
Find both dashes and take what lies between with MID: =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1) returns 2041 from NYC-2041-B. In Microsoft 365, =TEXTBEFORE(TEXTAFTER(A2,"-"),"-") is shorter.
Why does FIND return #VALUE!?
The text is not in the cell, or it is there with different capitals (FIND is case-sensitive). Wrap it to return something else: =IFERROR(FIND("-",A2),0).
How do I find the second occurrence of a character?
Start the search one character after the first match: =FIND("-",A2,FIND("-",A2)+1). The third argument is the position to start from.
