Excel functions / Text
FIND & SEARCH
Return the position of some text inside a cell.
Syntax
=FIND(find_text, within_text, [start_num])Arguments
find_text | The character or text to look for. |
|---|---|
within_text | The text to search in. |
start_num | Optional. The position to start searching from. |
How it works
FIND tells you where something appears, counting from 1. =FIND("@",B2) returns the position of the @ in an email address.
FIND is case-sensitive and does not allow wildcards. SEARCH works the same way but ignores case and does allow them.
On their own they return a number. They are most useful inside LEFT, MID and RIGHT, to cut text at a character instead of a fixed position.
Example
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | |||
| 2 | Nina Shah | nina.shah@example.com | 10 | |
| 3 | Omar Reid | omar@example.com | ||
| 4 | Mary Ann Lee | mary.lee@example.com | ||
| 5 |
Result: 10. The @ is the 10th character of the email.
Tips
- If the text is not found, both return #VALUE!.
- =ISNUMBER(SEARCH("tea",A2)) is a common way to test whether a cell contains a word.
Practise FIND & SEARCH
Try it yourself in a real spreadsheet, with instant feedback on every answer.