FIND
Return the position where one piece of text appears inside another.
Syntax
FIND(find_text, within_text, [start_num])| Argument | Required | Description |
|---|---|---|
find_text | Required | What to look for. |
within_text | Required | The text to look in. |
start_num | Optional | Which character to start from. Defaults to the first. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=FIND("sheet","Spreadsheet") | 7 | |
=FIND("SHEET","Spreadsheet") | #VALUE! | FIND is case-sensitive, and a miss is an error rather than zero. |
=SEARCH("SHEET","Spreadsheet") | 7 | SEARCH is the case-insensitive counterpart. |
=FIND(UPPER("sheet"),UPPER("Spreadsheet")) | 7 | Forcing one case does the same job, and works for comparisons too. |
=MID("a-b",FIND("-","a-b")+1,10) | b |
Two things surprise people
It is case-sensitive. FIND("SHEET", "Spreadsheet") does not match. When
you want case-insensitive matching, use SEARCH, which is the same function
without the case sensitivity:
=SEARCH("SHEET", "Spreadsheet") → 7
=FIND("SHEET", "Spreadsheet") → #VALUE!Forcing both sides to one case works too, and is worth knowing because it also makes comparisons and lookups case-blind:
=FIND(UPPER("sheet"), UPPER(A1))A miss is an error, not a zero. #VALUE! rather than 0, so a bare FIND
in a larger formula propagates the error. Wrap it when a miss is expected:
=IFERROR(FIND("-", A1), 0)That is one of the narrow cases where IFERROR is right —
the only error possible here is the one you mean.
Finding the second occurrence
start_num lets you resume past a previous hit, which is how you walk through
repeated separators:
=FIND("-", A1, FIND("-", A1) + 1)The position of the second hyphen. Nested one level deeper for the third, which
is the point at which SUBSTITUTE with an instance
number usually reads better.
Excel compatibility
Matches Excel for position, case sensitivity and the #VALUE! on no match. SEARCH provides the case-insensitive counterpart.