Here is the Excel formula to Check If Cell Contains Partial Text. You can se this formula to determine the cell contains partial text and Count, Sum and do further processing.

Excel if cell contains partial text

Below Formula will check if cell contains partial text. It will return ‘Yes’ if it contains the partial text, ‘No’ if not found.

=IF(COUNTIF(A2,"*PartialText*"),"Yes","No")

This formula will check for the partial text in cell A2 and returns ‘Yes’ if found, returns ‘No’ if not found. You can return True, False if required as shown in the given formula.

=IF(COUNTIF(A2,"*PartialText*"),TRUE,FALSE)

Excel if cell contains partial text wildcard

Following are the useful wildcards to help in matching the partial text in excel formulas, and very useful to check if a cell contains partial text in Excel.

Asterisk (*)

You can use Asterisk (*) to match zero or more characters. Check the examples below:

  • 1. Formula to check Excel if cell contains partial text, which should end with partial text
    =IF(COUNTIF(A2,"*PartialText"),TRUE,FALSE)

Here, * is the wildcard character preceding to the partial text.

  • 2. Formula to check Excel if cell contains partial text, which should begins with partial text
    =IF(COUNTIF(A2,"PartialText*"),TRUE,FALSE)

Here, * is the wildcard character following the partial text.

  • 3. Formula to check Excel if cell contains partial text, which contains the partial text at any position in the cell
    =IF(COUNTIF(A2,"*PartialText*"),TRUE,FALSE)

Here, * is the wildcard character following and preceding to the partial text.

Question mark (?) – one character

You can use Question mark (?) to match any one character. Check the examples below:

  • 4. Formula to check Excel if cell contains partial text, cell should end with partial text and only preceding any one character.
    =IF(COUNTIF(A2,"?PartialText"),TRUE,FALSE)
  • 5. Formula to check Excel if cell contains partial text, cell should begin with partial text and followed by any 3 character.
    =IF(COUNTIF(A2,"PartialText???"),TRUE,FALSE)
Excel if cell contains partial text

Share This Story, Choose Your Platform!

8 Comments

  1. Chris July 17, 2022 at 10:43 pm - Reply

    Hi, thanks for sharing this great tutorial. I have a question, how do you add multiple countifs in the same formula ? For example, if a cell contains some text, then display this…but if it contains another text, then display something else ? thanks!

    • PNRao September 11, 2022 at 6:44 am - Reply
      =IF(COUNTIF(A2,"*MyText*"),"String1-Yes",IF(COUNTIF(A2,"*AnotherText*"),"String2-Yes","No"))
      
  2. JM December 2, 2022 at 9:36 pm - Reply

    Can I use this formula for more than 4 countifs in the same formula? if so can you show the formula?

    • PNRao December 5, 2022 at 11:24 am - Reply

      If you wants to count based on multiple criteria, you can use one CountIFS formula with multiple criteria. Please let us know your requirement, so that we can help you!

  3. Simon June 1, 2023 at 12:21 pm - Reply

    Hi, Thnak for the nice overview. Can I use this to check whether a cell containing a long string contains a string value in another cell, instead of a manually typed value?

    Here’s a pseudo-example:
    =IF(COUNTIF(A1,”*contentofB1*”),”Yes”,”No”)
    … where Cell A1 has a long string and B1 has a shorter string, which might or might not be a sub-string somewhere in A1.

    Much appreciated!

    • PNRao June 2, 2023 at 2:38 am - Reply

      Yes!

      =IF(COUNTIF(A1,”*” &B1&”*”),”Yes”,”No”)

  4. Dave January 11, 2024 at 7:15 am - Reply

    Why do we have to use countif? Why can’t we just do =IF(A1, “*xyz*”, TRUE, FALSE)

    • PNRao March 28, 2024 at 1:27 pm - Reply

      COUNTIF function can use wildcard operator to match and compare the values.

Leave A Comment