How to / Text

How to check if a cell contains specific text in Excel

Use =ISNUMBER(SEARCH("urgent", A2)). It gives TRUE when the word appears anywhere in the cell, in upper or lower case.

Does the note mention "urgent"?

ABC
1Note
2Urgent: call backTRUE
3Invoice paid
4urgent - late

In C2: =ISNUMBER(SEARCH("urgent", A2)) → TRUE

How it works

  1. SEARCH looks for the word inside the cell and returns the position where it starts: a number.
  2. When the word is not there, SEARCH returns an error.
  3. ISNUMBER turns those two outcomes into TRUE and FALSE.

Variations

  1. Yes or No in place of TRUE or FALSE

    ABC
    1Note
    2Urgent: call backNo
    3Invoice paid
    4urgent - late

    In C2: =IF(ISNUMBER(SEARCH("urgent", A3)), "Yes", "No") → No

  2. Count the cells that contain it

    ABC
    1Note
    2Urgent: call back2
    3Invoice paid
    4urgent - late

    In C2: =COUNTIF(A2:A4, "*urgent*") → 2

    The asterisks stand for any text before and after the word.

  3. A case-sensitive check

    ABC
    1Note
    2Urgent: call backFALSE
    3Invoice paid
    4urgent - late

    In C2: =ISNUMBER(FIND("urgent", A2)) → FALSE

    FIND tells "urgent" from "Urgent"; SEARCH does not.

  4. Either of two words

    ABC
    1Note
    2Urgent: call backFlag
    3Invoice paid
    4urgent - late

    In C2: =IF(OR(ISNUMBER(SEARCH("urgent", A2)), ISNUMBER(SEARCH("late", A2))), "Flag", "") → Flag

  5. List the rows that contain it

    ABC
    1Note
    2Urgent: call backUrgent: call back
    3Invoice paidurgent - late
    4urgent - late

    In C2: =FILTER(A2:A4, ISNUMBER(SEARCH("urgent", A2:A4))) → Urgent: call back; urgent - late

    Excel 2021 and Microsoft 365.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related