Lessons Lesson 58

SEARCH: Case Insensitive, with Wildcards

About this lesson

SEARCH returns the position of one piece of text inside another. It ignores capitals, and it accepts wildcards where FIND does not.

By the end you can

  • search a cell without caring about case, using SEARCH.
  • use a wildcard in a search.

Practises SEARCH FIND ISNUMBER

By Ali, Founder, Excel Lessons

The idea

The arguments are FIND's arguments in FIND's order. So there is nothing new to learn about the shape. Two differences and no others: SEARCH ignores case, and SEARCH understands wildcards. A question mark stands for one character, and an asterisk for any run of them.

Choose between them on purpose, not by habit. Case matters more often than people expect. Product codes where a lower-case suffix means something different, or a match against a column of initials. If the case is meaningful, FIND is the correct function, and SEARCH will find the wrong row without any warning. If it is not, SEARCH saves you from a column of #VALUE! caused by somebody typing in lower case. The most common use of either is wrapped in ISNUMBER. That turns "a position or an error" into "yes or no".

The mistake to watch for

Using SEARCH when the case is part of the meaning. It ignores capitals. So a product code with a lower-case suffix that means something different matches the wrong row, with no warning. When capitals carry meaning, FIND is the right function. When they do not, SEARCH saves a column of #VALUE! caused by somebody typing in lower case.

Read more about this

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

SEARCH finds text like FIND, but ignores capitals.

In B2, type:

=SEARCH("invoice",A2)

B2
Row A B C D E F G
1 Subject line Position
2 RE: Invoice INV-2231 overdue
3 Fwd: invoice query from Halston
4 Delivery note DN-0088
5
6 Looking for invoice
7
8

Every step

  1. 1

    SEARCH finds text like FIND, but ignores capitals. In B2, type: =SEARCH("invoice",A2).

    Hint. Same shape as FIND.

  2. 2

    Read =SEARCH(B6,A3) and say what it returns for the forwarded message.

    Hint. The first argument is a reference this time.

  3. 3

    SEARCH accepts wildcards where FIND does not. In B4, find where INV- plus any four characters begins in A2. Remember: ? stands for one unknown character.

    Hint. Four question marks after the hyphen.

  4. 4

    In B3, use SEARCH to find where "from" begins in A3.

    Hint. The word is four characters long, but its length is not what you are asked for.

  5. 5

    A delivery note has no invoice in it. Read =ISNUMBER(SEARCH("invoice",A4)) and say what it returns.

    Hint. ISNUMBER asks whether what it is given is a number.