Excel Practice
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

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.

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

Find where the word invoice appears in A2, ignoring whether it is capitalised. Put the position in B2.

To begin, type it exactly:

=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

    Find where the word invoice appears in A2, ignoring whether it is capitalised. Put the position in B2. To begin, type it exactly: =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 understands wildcards. In B4, find where a reference like INV- followed by any four characters starts in A2. Use a question mark for each unknown character.

    Hint. Four question marks after the hyphen.

  4. 4

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

    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.