FIND returns the starting position of text inside another string. It is case-sensitive and counts from one. To search the sheet itself, use the Find keyboard shortcut instead.
FIND function syntax
=FIND(search_for, text_to_search, [starting_at])- search_for: text to locate.
- text_to_search: containing text.
- starting_at: optional starting character position; defaults to one.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Text |
|---|
| banana |
| alex@example.com |
| John Michael Smith |
| Emily Davis |
| no email yet |
Find the first matching character
Enter this formula in A10. The first lowercase a in banana is the second character. FIND returns its position, not the character itself.
=FIND("a",A2)Result: 2.
Locate the at sign
The at sign separates the username and domain. Finding it does not prove an address is valid or deliverable.
=FIND("@",A3)Result: 5.
Find the second space
The inner FIND locates the first space. Adding one starts the outer search after it. The answer still counts from the beginning of the original text.
=FIND(" ",A4,FIND(" ",A4)+1)Result: 13.
Extract the first name
Subtract one to exclude the separating space. This assumes the first name is the text before the first space; names do not all follow that pattern.
=LEFT(A5,FIND(" ",A5)-1)Result: Emily.
Handle a missing substring
A missing substring produces #VALUE!. IFERROR provides a label, but it also hides other errors, so inspect the inputs if results look wrong.
=IFERROR(FIND("@",A6),"No at sign")Result: No at sign.
Choose case-sensitive or insensitive matching
Uppercase A is absent from banana, so FIND errors and ISNUMBER returns FALSE. SEARCH ignores case when that better matches your task.
=ISNUMBER(FIND("A",A2))Result: FALSE.
Other Google Sheets articles you may also like