How to Check, Highlight & Return Value If Cell Contains Text?

Here’s an overview of a simple IF check that returns a predetermined pair of values.

Download the Practice Workbook

Excel Condition If Cell Contains Text: 13 Useful Applications

Method 1 – Use the IF Function to Check If the Cell Contains Text and Return a Value in Excel

You can use the IF function to check if the cell contains text and then return a value. If the condition is met, it returns a TRUE value, otherwise it returns a FALSE value.

• Insert the following formula in cell D5 and press Enter.
• Use the AutoFill tool to apply the same formula in the cells D5:D13.

• As a result, Available or Not Available will appear in the status column.

Method 2 – Use IF and ISTEXT Functions to Check If the Cell Contains Text and Return a Value

The ISTEXT function checks whether a value is text, and returns TRUE or FALSE.

• Insert the following formula in cell D5 and press Enter.
• Use the AutoFill tool to apply the same formula in the cells D5:D13.

• This formula will only return values for cells that have text values.

Method 3 – Apply ISNUMBER and SEARCH Functions to Find If a Cell Contains Specific Text

The ISNUMBER function checks whether a value is a number and returns TRUE or FALSE.

The SEARCH function returns the number of characters at which a specific character or text string is first found, reading left to right (not case-sensitive).

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.
• True or False appear in the status column.

Method 4 – Return a Given Value If a Cell Contains Specific Text

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.
• We will get our desired output.

Method 5 – Return a Value If a Cell Contains Case-Sensitive Specific Text

The EXACT function checks whether two text strings are exactly the same and returns TRUE or FALSE. It is case-sensitive.

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.
• We will get the value Available in cells with the exact text “Bars”.

Method 6 – Add 1 If a Cell Contains Specific Text in Excel

We’ve added two new columns titled Amount and Updated Amount. We’ll add 1 to the Amount and show that in Updated Amount if the cell passes the check.

• Insert the following formula in cell E5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells E5:E13.

Method 7 – Check If the Cell Contains Partial Text

We’ll look for partial matches with the text “Cra”.

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.
• As a result, we will get the value Yes in cells with the partial text “Cra”.

Method 8 – Return a Value in Another Cell Using INDEX and MATCH Functions If the Cell Contains Text

We have slightly altered the dataset.

• Insert the following formula in cell C15 and press Enter.

• We will get our product name “Carrot” that correlates to the row that contains “Bars” in column B.

Method 9 – Return TRUE If Cell Contains Text in Excel Using a Combination of IF, ISERROR, and FIND Functions

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.
• The range D5:D13 returned TRUE or FALSE based on whether there is text in each cell in the range B5:B13.

Read More: IF-THEN Statements with Text

Method 10 – Highlight a Cell If It Contains Specific Text in Excel

We want to highlight a cell that contains “Bars”.

• Select the entire dataset.
• Go to the Home tab.
• Select Conditional Formatting and choose New Rule.

• Select Use a formula to determine which cells to format under the Select a Rule Type section from the New Formatting Rule window.
• Insert the following formula in the Format values where this formula is true section.

• Click on Format to choose a fill color and click OK.
• Click OK to apply formatting.

• We have highlighted the cells that contain specific text “Bars”.

Read More: If Cell Contains Text Then Copy to Another Sheet

Method 11 – Copy to Another Sheet Using the FILTER Function If a Cell Contains Text

We want to copy the text string “Crackers” and its corresponding product from the FILTER Sheet to the new FILTER(2) Worksheet.

• Use the following formula in cell B5 of the sheet FILTER(2) and press Enter.

• We will get the product names for the category “Crackers”.

Read More: If Cell Contains Text Then Return Value in Another Cell Using Formula

Method 12 – Return a Value If a Cell Contains One of Many Text Strings

We want to search cells that contain at least one text string between “Bars” or “Veg”.

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.

Read More: If Cell Contains Specific Text Then Add 1

Method 13 – Return a Value If a Cell Contains Several of Many Text Strings

We want to search cells that must contain both text strings of “Bars” and “Chlt”.

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.

Read More: Check If Cell Contains Text Then Return Value

How to Return a Value If a Cell Does Not Contain Specific Text?

• Insert the following formula in cell D5 and press Enter.

• Use the AutoFill tool to apply the same formula in the cells D5:D13.

Read More: Return TRUE If Cell Contains Text

If a Cell Contains Text in Excel: Knowledge Hub

<< Go Back to If Cell Contains | Formula List | Learn Excel