Excel If Cell Contains Text

You can use the Excel If Cell Contains Text Then Formula in order to get the result back if the cell contains any text or a certain text. If one cell has a certain string or text, another cell will display the results.

How to Use The Excel If Cell Contains Text Formula

If cell A2 contains the text “Can” you can use the IF statement to determine whether or not to display the corresponding “Yes” or “No” in another cell.

Here are some sample Formulas to determine whether or not a cell has text and then return the appropriate value.

If Cell Contains Text Then TRUE

The Excel formula to return True if a Cell has Specific Text can be seen below. A cell can be checked to see if a specific string is present, with the result being either True or False.

=IF(ISNUMBER(FIND("Can",A2,1)),TRUE,FALSE)

In this instance, we conduct our search in Cell A2. The formula for cells A3, A4, A5, and A6 will be automatically updated when dragged and dropped from the bottom right corner of the first cell.

The cell must contain the specific text in order for us to return a “True” value. Otherwise, we can just return a “False” value.

In the following example, we will return a TRUE result if the cells A2:A6 contain the word “Can”. In cases where the specific text could not be located, the value FALSE would be displayed.

This Excel formula is case-sensitive. If you don’t want this formula to be case-sensitive, replace the FIND function with the SEARCH function as follows:

=IF(ISNUMBER(SEARCH("can",A2,1)),TRUE,FALSE)

If Cell Contains Text Then Return a Value

If cell contains a text, we can return a certain value (other than True or False). Here is the Excel formula to get a value if a Cell has text in it.

=IF(ISNUMBER(SEARCH("can",A2,1)),"Found","Not found")

Tips: Don’t forget to replace the partial text between “” (in our case “can”) and the serching cell name (A2 in our case) according to your Excel sheet.

If Cell Contains Text from Another Cell Then Return True or False

If you already have a column with cells that contain specific text and you want to use it dynamically, then I recommend using the following formula.

=ISNUMBER(SEARCH(D2,A3))

In our case, we look for partial text located in cell D2 in the other cells located in column A.

If Cell Contains Partial Text

1. If Cell Contains Partial Text which contains the text at any position in the cell

To see if a cell in Excel contains a partial text, use the below mentioned formula. Using the following formula, if the search results contain the partial text, it will return “Yes,” else it will return “No”.

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

In our scenario, the answer “Yes” is shown if a cell contains “coun” as a partial text. This method can be used to your situation. The “Yes” and “No” responses given as “then” replies could also be changed.

To match zero or more characters, use an asterisk (*). Consider the following examples:

2. If the cell contains partial text at the end of the cell

If the cell contains partial text that should be closed with that partial text, you can use the following formula:

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

In this case, we will look for the partial text “ster” which must be at the end of the sentence. If this partial word is at the beginning or in the middle of the sentence in cells A2:A6, “FALSE” will be displayed.

3. If the cell contains partial text to start with

This partial text must be at the beginning of the cell in your Excel sheet. If the cell contains partial text that the cell should start with, then you should use the following formula:

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

In my example, I will use the partial text “sis” found in cells A3 and A7. So, if the cells in column A (A2:A7) contain the partial text “sis” at the beginning, “TRUE” will be displayed. Otherwise, “FALSE”.

Sum If Cell Contains Partial Text

Are you interested to sum the cells depending on partial match criteria using SumIfs? If a cell contains partial text, the following Excel formula sums the values:

=SUMIF($A$2:$A$7,"*"&D2&"",$B$2:$B$7)

In our case, we are looking for the text in cell D2 in the cell range A2:A7. Positive results (where partial text is found) automatically form the sum of the values found in column B.

So, if you look carefully, SUMIF only adds up if the partial text search condition is met.

How it Works: Cell contains specific text Formula

You can use the SEARCH function in conjunction with the ISNUMBER function to determine if a cell contains specific text in Excel.

=IF(ISNUMBER(SEARCH(substring,text)), "Yes", "No")

Instead of returning TRUE or FALSE, the preceding formula will return “Yes” if the specific text is found and “No” otherwise.

To make this formula case-sensitive, replace the SEARCH function with the FIND function as follows:

=ISNUMBER(FIND(substring,text))