SEARCH
Finds the position of text inside other text, ignoring case. Often used to check whether a name contains a certain word.
SEARCH(find_text, within_text, [start_num])- [ ]optional part
- find_text
- What to look for: a word or character in quotes.
- within_text
- Where to look, for example the product name in A2.
- start_num
- Which character to start searching from. If left out, the search starts from the first one.Optional
Return value
A number: the position where the text starts, counting from 1. Case doesn't matter. If the text isn't found, you get #VALUE!.
Examples
Does the name contain "coffee"
fx
A
B
C
1
2
3
4
5
6
7
8
Product
Is it coffee?
Ground coffee 8 oz
Green tea, 100 bags
Whole bean COFFEE 2 lb
Cappuccino pods
Instant coffee 3 oz
Sheet1
Details
SEARCH works like FIND but doesn't tell uppercase from lowercase: "coffee" is found both in "Ground coffee" and in "COFFEE".
If the text isn't found, SEARCH returns #VALUE!. That's why, to check "is the word there or not", you wrap it in ISNUMBER: a found position gives TRUE, an error gives FALSE.
SEARCH understands wildcards: ? stands for any single character, * for any number of characters.
Common mistakes
- The word was found inside another word: "tea" is also found in "steamer".
- Where the word is missing, you see #VALUE!. Wrap SEARCH in ISNUMBER or IFERROR.
- You need to find the * or ? character itself, but SEARCH treats it as a wildcard. Put a tilde before it: "~*".