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

In this Excel tutorial, we will cover all the scenarios we need to know if a cell contains text in Excel. We will discuss 13 different scenarios where we will use several Excel functions. We will also explain how to copy text into another sheet using formulas if a cell contains text. We will also show you how to highlight cells based on other Excel cell values.

When preparing this article, we used Microsoft 365, but the operations apply to all versions.

Sometimes we feel the need to identify in Excel if a cell contains text or not. So if our dataset has text in cells and we are working with Microsoft Excel, this article will be helpful with all answers.

Download Practice Workbook

Excel Condition “If Cell Contains Text”: 13 Useful Applications

Now, we are going to discuss 13 different applications you can use when a cell contains text in Excel. Here are a few of them:

• Using the IF function to return value if a cell contains a specific text
• Using a combination of the IF and ISTEXT functions to check if a cell contains text
• Applying a combination of the ISNUMBER and SEARCH functions to check if the cell contains text
• Returning a value if a cell contains a specific text bot case-sensitive and insensitive

and a few more scenarios.

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 or not and then return a value. If the condition is met, it returns a TRUE value, otherwise it returns a FALSE value.

Click the image to enlarge

Now follow the below steps:

• 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 on the status column.

2. Use IF and ISTEXT Functions to Check If the Cell Contains Text And Return Value

You can also apply IF and ISTEXT functions to check if a cell contains text and then return a value. The ISTEXT function checks whether a value is text, and returns TRUE or FALSE.

Here are the required steps you need to follow:

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

• Thus, the formula will only return values to those cells that have text values.

3. Apply ISNUMBER and SEARCH Functions to Find If a Cell Contains Specific Text Or Not

To find if a cell contains text or not, you can apply the ISNUMBER and SEARCH functions. The ISNUMBER function checks whether a value is a number, and returns TRUE or FALSE.

On the other hand, 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).

Click the image to enlarge

If we apply the ISNUMBER & SEARCH functions together, the ISNUMBER function will return True or False depending on whether the SEARCH function finds a match. Here are the required steps:

• 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, True or False returns on the status column.

4. Return a Given Value If Cell Contains Specific Text

You can return a value if a cell contains some specific text. You can use the IF function for this purpose. Here are the necessary steps:

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

• Thus we will get our desired output.

5. Return Value If Cell Contains Case-Sensitive Specific Text

In this method, the specified value is returned only if it matches exactly. Here, we will apply IF and EXACT functions. The EXACT function checks whether two text strings are exactly the same, and returns TRUE or FALSE. It is case-sensitive.

Now follow the below steps:

• 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”.

6. Add 1 If Cell Contains Specific Text in Excel

Using the IF function, you can easily determine whether a cell has a specific text or not, and then add 1 by using the IF function’s inputs. To describe the process, we’ve added two new columns titled Amount and Updated Amount. We will show the final output in the Updated Amount column.

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

7. Check If Cell Contains Partial Text Or Not in Excel

Suppose we are searching for those categories that have partial matches with the text “Cra”. To find out this you can use IF and COUNTIF functions together. Follow the steps below:

• 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”.

8. Return Value in Another Cell Using INDEX and MATCH Functions If Cell Contains Text

Sometimes, we have a criterion in a range of cells to match the result in another range of cells. In that case, we can use the INDEX function to match a text in a range and the MATCH function to show the value in another cell. We have slightly altered the dataset to do that.

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

• In this way, we will get our product name “Carrot”.

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

We can join functions like IF, ISERROR, and FIND to determine whether a cell has text value or not and then return TRUE or FALSE based upon it. So, follow the below steps to return TRUE if the cell contains text in Excel.

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

• Here, 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

10. Highlight Cell If It Contains Specific Text in Excel

In this section, we want to highlight a cell that contains specific text. Suppose the text is “Bars”. Here are the steps that you need to follow:

• First, select the entire dataset.
• After that, go to the Home tab>>Conditional Formatting>>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 finally click OK.

• As a result, we have highlighted the cells that contain specific text “Bars”.

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

11. Copy to Another Sheet Using FILTER Function If Cell Contains Text

Yes, it is possible to copy to another sheet using the FILTER function if cell contains text. Suppose we want to copy the text string “Crackers” and its corresponding product from the FILTER Sheet to the new FILTER(2) Worksheet. For this purpose, we need to use the FILTER function.

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

• As a result, we will get the product names for the category “Crackers”.

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

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

We can also return a value if cell contains one of many text strings. Suppose, we want to search cells that contain at least one text string of “Bars” or “Veg” text. We can easily do this task by applying few functions together. Here are the required steps:

• 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

13. Return a Value If Cell Contains Several Of Many Text Strings

Suppose you want to search cells that must contain both text strings of “Bars” and “Chlt”. Like the previous method, you can apply ISNUMBER and SEARCH to match a text, then use AND function instead of OR to match both text strings.

• 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 Cell Does Not Contain Specific Text?

Suppose you want to find cells and return values that don’t contain a specific text. To do that follow the below steps:

• 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

What Are the Key Takeaways from This Article?

• Here, I have discussed some scenarios if a cell contains text in Excel.
• Explained a total of 13 different scenarios if a cell contains text.
• Discussed how to return a value if a cell does not contain specific text in Excel.
• Showed highlighting cell if it contains specific text in Excel.
• Provide solutions to frequently asked questions by readers.

Frequently Asked Questions

1. How do I condition if a cell contains text in Excel?

Answer: I have already discussed some scenarios in this article about how you can apply conditions if a cell contains text in Excel. Please go through the article carefully.

2. Can you use the IF function in Excel with text?

Answer: Yes, you can definitely use the IF function in Excel with text. Go through the above-discussed scenarios thoroughly. I hope you will get your answer.

3. What does ISTEXT do in Excel?

Answer: The ISTEXT function in Excel checks whether a value is text or not. If text, then it returns TRUE. Otherwise, FALSE. In your dataset, you can use this function to check whether a cell contains text or not.

If Cell Contains Text in Excel: Knowledge Hub

Conclusion

In this article, we have covered all the possible scenarios you need if the cell contains text in Excel. With several Excel functions, such as IF, ISTEXT, ISNUMBER, and SEARCH, we have demonstrated 13 useful applications when an Excel cell contains text. We hope this article was informative and useful for you. For any further queries, comment below.

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