SEARCH

Returns the position of a string of text within another string.

Syntax:

SEARCH(findtext, texttosearch, startposition)

returns the character position of the first occurrence of findtext within texttosearch.

startposition (optional) is the position from which the search starts.

The search is not case-sensitive.

A failed search gives the #VALUE! error.

Example:

SEARCH("yo", "Yoyo")

returns 1. The search is case-insensitive.

SEARCH("cho", "choochoo", 2)

returns 5.

SEARCH("t.n", "often")

returns 3, if regular expressions are enabled. The "." stands for any single character in a regular expression, so "t.n" matches "ten".

SEARCH("xyz", "abcdef", 1)

returns #VALUE!.

NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.

IF( SEARCH("xyz","abcdef",1) , "Substring Present", "ERR: Missing Substring" )

returns #VALUE! which is not very useful, therefore we could use either ISERROR() or ISNUMBER() for example:

IF( ISERROR( SEARCH("xyz","abcdef",1) ) , "ERR: Missing Substring", "Substring Present" ).

returns "ERR: Missing Substring" ... allowing the IF() to function, and not propagating the error from the SEARCH() function.

IF( ISNUMBER( SEARCH("xyz","abcdef",1) ) , "Substring Present", "ERR: Missing Substring" ).

returns "ERR: Missing Substring" ... allowing the IF() to function, and not propagating the error from the SEARCH() function.

NOTE: In practice, it may be more maintainable to use ISNUMBER() to avoid negative logic, and it is more indicative of the evaluation desired: if the substring has a position, then ISNUMBER() is TRUE, else ISNUMBER() is FALSE.

The Formula:

In cell C1, you would enter the following formula:

SEARCH("Acme", B1)

Explanation:

• "Acme": This is the text you are looking for. The SEARCH function is case-insensitive, so it will find "acme," "Acme," "ACME," etc.
• B1: This is the cell containing the text you want to search within (the Product Description).

How It Works:

• For cell C1: The formula finds "Acme" at the very first character of the string "Acme-Brand Power Drill". The SEARCH function returns the starting position of the text, which is 1.
• For cell C2: The formula cannot find "Acme" within the string "Heavy-Duty Wrench". When the text is not found, SEARCH returns a #VALUE! error.
• For cell C3: The formula finds "Acme" at the first character of "Acme-Brand Sanding Disc Pack" and returns 1.
• For cell C4: "Acme" is not found, so it returns #VALUE!.
• For cell C5: "Acme" is found at the first character, and the function returns 1.

Adding Logic (Combining with IFERROR):

To make this data more useful and easier to filter, you would typically wrap the SEARCH function in an IFERROR or ISNUMBER function to get a simple TRUE/FALSE or YES/NO result.

The Improved Formula:

This formula is a bit complex and has a subtle detail that's important to understand. Let's break it down from the inside out to see what each part does.

The formula is IFERROR(IF(SEARCH("Acme", B2), 1), "#VALUE!")

1. The Inner Part: SEARCH("Acme", B2)

• Function: SEARCH
• What it does: It looks for the text "Acme" within the text in cell B2.
• Possible Outcomes:
• • If found: It returns the starting position of the text. For example, if "Acme" is at the beginning, it returns 1. If it's in the middle, it returns a different number.
  • If not found: It returns a #VALUE! error.

2. The Middle Part: IF(SEARCH("Acme", B2), 1)

• Function: IF
• What it does: This function evaluates a logical test.
• How it works here: The logical test for this IF function is the result of SEARCH("Acme", B2).
• • IF SEARCH returns a number (e.g., 1): Any non-zero number is treated as TRUE in a logical test. So, the IF function's condition is met. It then returns the value_if_true, which is 1.
  • IF SEARCH returns a \#VALUE! error: The IF function's logical test fails, and since no value_if_false is specified, the IF function itself returns a \#VALUE! error.

3. The Outer Part: IFERROR(..., "#VALUE!")

• Function: IFERROR
• What it does: This function catches and handles errors from the formula inside it.
• How it works here:
• • If the middle IF function returns 1: The IFERROR function sees a valid result, not an error. It simply returns that result, which is 1.
  • If the middle IF function returns a \#VALUE! error: The IFERROR function catches this error. It then returns its second argument, the value_if_error, which is the text string "#VALUE!".

Summary of the Formula's Logic

This formula effectively translates to:

• If "Acme" is found in cell B2, the formula returns the number 1.
• If "Acme" is NOT found in cell B2, the formula returns the text string "#VALUE!".