The FIND and SEARCH functions in Excel
FIND returns the position of a character or word inside a text: the @ in an e-mail address is at position 12. On its own that's rarely the goal; it's the helper that tells LEFT and MID where to cut.
SEARCH does the same but ignores upper and lower case and allows wildcards. If nothing is found, both return #VALUE!.
Syntax
=FIND(find_text, within_text, [start_num])
| Argument | What it means |
|---|---|
| find_text | What to look for, e.g. "@" or " ". |
| within_text | The text or cell to search in. |
| start_numoptional | Where to start searching. Default 1. Use it to find the second occurrence. |
Examples
1. Position of a character
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | @ position | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | 12 |
"omar.haddad" is 11 characters, so the @ is the 12th.
2. The second occurrence
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | 2nd dash | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | 8 |
The first dash is at 3, so start looking at 4: the next one is at 8.
3. Ignore case: SEARCH
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | "MAIL" at | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | 17 |
SEARCH finds "mail" in shopmail.com although the case differs. =FIND("MAIL",B2) would return #VALUE!.
Common mistakes
#VALUE! error. The text wasn't found, often because of a case difference with FIND.
Fix: Use SEARCH to ignore case, or
=IFERROR(FIND("@",B2),0)when it may be missing.Off by one when cutting.
=LEFT(A2,FIND(" ",A2))includes the space itself.Fix: Subtract 1 for LEFT, add 1 for MID.
Frequently asked questions
How do I check if a cell contains a word?
=ISNUMBER(SEARCH("mail",B2)) returns TRUE when the word appears anywhere, in any case.
Related functions
- LEFTLEFT returns the first characters of a text, RIGHT the last ones.
- MIDMID returns characters from the middle of a text: a start position and a length.
- TEXTAFTERTEXTAFTER and TEXTBEFORE split text at a delimiter, such as the @ in an e-mail or a dash in a code, without counting positions.
- SUBSTITUTESUBSTITUTE replaces specific text inside a cell with other text, everywhere or only one occurrence.