Excel SEARCH functions: Part Two: The SEARCH function

As explained in part one, the SEARCH function in Excel is very similar to FIND because it also returns the location of a substring in a text string.

The syntax and arguments are akin to those of FIND.

1 Syntax

The breakdown of this is:

• Find_text indicates the text to find.
• Within_text indicates the text to search within.
• Start_num is an optional argument that specifies from which position the search shall begin in the text. If omitted, the search starts from the 1st position in the text.

If the SEARCH function does not find the find_text character(s), a #VALUE! error is returned.

2 Difference with find function

• Not case-sensitive.
• Allows the use of wildcards in find_text (i.e. “?”, “*” and “~”).
  • “?” is used to find a single character.
  • “*” is used for match sequence.
  • If you want to search the “*” or “?” the use the “~” before character.

3 Examples

1. Suppose you want to find the word “car” in “motorcar”.
   The formula =SEARCH (“car”; “motorcar”) returns 6 because the substring begins at the sixth character of the word “motorcar”.
2. Suppose you want to find the letter “e” in “Examples”.
   The formula =SEARCH (“e”; “Examples”) returns 1 because “e” is the first character in the word “Examples”, ignoring the case. The default search position = 1. Suppose the formula was = SEARCH (“e”; “Examples”;2). This returns 7 because the second “e” in the word “Examples” is the seventh character.

When a value is not found, the result will be an error. This was also the case in the FIND function. The function =SEARCH(“F”;”OPERAHOUSE”) gives an error as result!

Figure 1: Examples in practice – SEARCH