How to Use FIND in Google Sheets
=FIND(search_for, text_to_search, [starting_at]). It is case sensitive.
What FIND does
FIND tells you where something is inside a piece of text. That position is most useful as input for other functions, such as LEFT, RIGHT and MID, when you need to cut text at a character that is not always in the same place.
FIND syntax
=FIND(search_for, text_to_search, [starting_at])
search_for: the text to locate.text_to_search: the text to look in, usually a cell.starting_at(optional, default 1): the position where the search begins.
Google's help page notes that FIND is case sensitive (use SEARCH for a case-insensitive search), returns #VALUE! if nothing matches, and that its argument order differs from SPLIT and SUBSTITUTE: the text you look for comes first.
A step-by-step example
Cell A2 holds the code INV-2026-0042.
| A | |
|---|---|
| 1 | Code |
| 2 | INV-2026-0042 |
To find the first hyphen:
=FIND("-", A2)
This returns 4. The characters are I (1), N (2), V (3) and the hyphen (4).
Start the search later
Use the third argument to skip ahead. To find the next hyphen, begin the search after the first one:
=FIND("-", A2, 5)
This returns 9. Together with MID, these positions let you extract the text between the two hyphens, even when its length changes from code to code.
Common mistakes
- Case.
FIND("inv", A2)returns #VALUE! because the text is uppercase. - Swapped arguments. The text to look for goes first, and the cell second.
- A missing match. Wrap FIND in IFERROR to show something friendlier than #VALUE!.
- Expecting a position count from 0. The first character is position 1.
Try it yourself
Use the code above (A2 holds INV-2026-0042). Type your formula into the box, starting with =, then press Check.
Exercise 1: First hyphen
Write a formula that returns the position of the first hyphen in A2.
Exercise 2: Second hyphen
Write a formula that returns the position of the second hyphen in A2 by starting the search after the first one (which is at position 4).
What to learn next
- LEFT, RIGHT and MID: pull part of a text string by position.
- SUBSTITUTE: replace text inside a string.
- IFERROR: replace an error with a clean result.
- REGEXEXTRACT: pull text out with a pattern.
- LEN: count the characters in a cell.
- SEARCH: like FIND, but not case sensitive.
Syntax and rules in this guide were checked against Google's FIND help page. See all guides.