SEARCH

Return the position of one piece of text inside another, without caring about capitalisation.

Syntax

SEARCH(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.31.0 — not transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.

Formula	Result	Notes
`=SEARCH("SHEET","Spreadsheet")`	`7`	Case is ignored, which is the whole difference from FIND.
`=FIND("SHEET","Spreadsheet")`	`#VALUE!`	For contrast — FIND is case-sensitive.
`=SEARCH("e","Spreadsheet",5)`	`9`	Starting at 5 skips the e at position 4.
`=SEARCH("zz","Spreadsheet")`	`#VALUE!`	A miss is an error, not a zero.
`=SEARCH("s?eet","Spreadsheet")`	`7`	? stands for exactly one character.
`=SEARCH("*sheet","Spreadsheet")`	`1`	* stands for any run, so the match starts at the beginning.
`=SEARCH("~?","a?b")`	`2`	A tilde escapes a wildcard, finding a literal question mark.

SEARCH or FIND

The same function twice, with one difference each way:

	Case	Wildcards
`SEARCH`	ignored	`?` and `*`
`FIND`	matters	never supported

Use SEARCH when the data’s capitalisation is inconsistent — imported names, free-typed codes, anything a person entered. Use FIND when case is meaningful, which is rarer than it sounds.

Wildcards

? matches exactly one character, * matches any run of them including none:

=SEARCH("s?eet", "Spreadsheet")   → 7    s, any char, then eet
=SEARCH("*sheet", "Spreadsheet")  → 1    match starts at the beginning

Note what * does to the position. SEARCH returns where the match starts, and a leading * can match nothing at all, so the answer is 1 rather than the position of sheet. If you want where the literal text begins, do not lead with a wildcard.

Searching for a literal ? or *

Prefix it with a tilde:

=SEARCH("~?", "a?b")   → 2

Without the tilde, ? would match the a. This is the one piece of the syntax nobody guesses, and it matters for any data with real question marks in it.

FIND has no wildcards at all and never had — FIND("s?eet", …) looks for a literal question mark and fails. That is Excel’s behaviour too, and it is the reason to reach for FIND when your search text might itself contain ? or *.

A miss is an error

Like FIND, no match gives #VALUE! rather than 0. Wrap it when a miss is expected:

=IFERROR(SEARCH("-", A1), 0)

Excel compatibility

Matches Excel for case-insensitive matching, start_num, ? and * wildcards, the tilde escape, and the #VALUE! on no match.