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)
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!
Can I use this formula for more than 4 countifs in the same formula? if so can you show the formula?
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!
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!
Yes!
=IF(COUNTIF(A1,”*” &B1&”*”),”Yes”,”No”)
Why do we have to use countif? Why can’t we just do =IF(A1, “*xyz*”, TRUE, FALSE)
COUNTIF function can use wildcard operator to match and compare the values.