30 Excel Functions in 30 Days: 18 – SEARCH

Excel SEARCH Function

Yesterday, in the 30XL30D challenge, we identified errors with the ERROR.TYPE function, and saw that it could help with Excel troubleshooting.

SEARCH Function

For day 18 in the challenge, we’ll examine the SEARCH function. It looks for a character, or characters, within a text string, and tells you where it was found. We’ll see how to handle any errors that it returns.

So, let’s take a look at the SEARCH information and examples, and if you have other tips or examples, please share them in the comments.

Function 18: SEARCH

The SEARCH function looks for a text string, within another text string, and returns its position, if found. It is not case sensitive.

Search00

How Could You Use SEARCH?

The SEARCH function looks for a text string, within another text string, and it can:

  • Find a text string within another text string – not case sensitive
  • Use wildcards in the search
  • Specify a start number within search text

SEARCH Syntax

The SEARCH function has the following syntax:

  • SEARCH(find_text,within_text,start_num)
    • find_text is the text that you’re looking for.
    • within_text is the string that you’re searching in.
    • if start_num is omitted, the search starts with the first character

SEARCH Traps

The SEARCH function will return the position of the first matching string, regardless of case. If you need a case sensitive search, use the FIND function, which we’ll see later in the 30XL30D challenge.

Example 1: Find Text in a String

Use the SEARCH function to look for text within a text string. In this example, we’re looking for a single character (entered in cell B5), within a text string in cell B2.

=SEARCH(B5,B2)

If the text is found, the SEARCH function returns the number of its starting position in the text string. If it’s not found, the result is a #VALUE! error.

You could use IFERROR to wrap the SEARCH function, and display a message, if the result is an error. The IFERROR is available in Excel 2007 and later versions. For earlier versions, you can use IF with ISERROR.

=IFERROR(SEARCH(B5,B2),”Not Found”)

Find Text in a String with SEARCH Function
Find Text in a String with SEARCH Function

Example 2: Use wildcards with SEARCH

Another way to check the SEARCH results is with the ISNUMBER function. If the string is found, the SEARCH result is a number, so the ISNUMBER result is TRUE. If the text is not found, SEARCH results in an error, and ISNUMBER returns FALSE.

You can also use wildcards in the find_text argument. The * (asterisk) represents any number of characters, or no characters, and the ? (question mark) represents a single character.

In this example, the * wildcard is used, so central, center and centre are all found in the street addresses.

=ISNUMBER(SEARCH($E$2,B3))

Search02

Example 3: Specify Start Number for SEARCH

By typing two minus signs (double unary) in front of the ISNUMBER function, it returns 1/0 instead of TRUE/FALSE. Then, a SUM function in cell E2 can total the number of records where the text string was found.

In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1. The formula in cell C2 is:

=–ISNUMBER(SEARCH($E$1,B2))

The formula found the string that contain “bank”, but one of those is in a City name, not the occupation:

Search03a

There is a pipe character after each city name, so we can add a SEARCH for that. Its position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching.

Now, with the revised formula, only the rows with “bank” in the occupation are counted.

=–ISNUMBER(SEARCH($E$1,B2,SEARCH(“|”,B2)))

Search03b

Download the SEARCH Function File

To see the formulas used in today’s examples, you can download the SEARCH function sample workbook. The file is zipped, and is in xlsx file format, with no macros.

Watch the SEARCH Video

To see a demonstration of the examples in the SEARCH function sample workbook, watch this short Excel video tutorial.

_____________

4 thoughts on “30 Excel Functions in 30 Days: 18 – SEARCH”

  1. SEARCH in conjunction with LOOKUP and a BigNum criteria (see Day 16) can prove very useful

    Consider:

    Keywords in C1:C10 with their associated results in D1:D10
    In A1:A100 we have various phrases
    In B1:B100 we want to identify if any given keyword exists in the phrase and if so retrieve the associated results

    =LOOKUP(9.99E+307,SEARCH(C1:C10,A1),D1:D10)

    The above is a much simplified example:

    – We might need to account for keywords that are not mutually exclusive (and order our keywords appropriately in order of least preference)
    – We might choose to add some delimiters to both keywords and phrase to reduce risk of false positive results
    – We might choose to set C1 as * and D1 as “No Keyword Found” to act as an Error Handler should no keyword be found in the phrase.

    If it’s possible for our keyword range to occasionally contain blanks we can handle by introducing a REPT function (Day 15)

    =LOOKUP(9.99E+307,SEARCH(INDEX(C1:C10&REPT(“ZZZZ”,C1:C10=””),0),A1),D1:D10)

    So here we append our blanks such that they become ZZZZ … as a result of which the SEARCH will return a #VALUE! error and they will be ignored.

  2. […] day 23 in the challenge, we’ll examine the FIND function. It’s similar to the SEARCH function, which we saw on Day 18, but the FIND function is case […]

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.