How to Check If Cell Contains Text Then Return Value in Excel - ExcelDemy (2024)

Excel has various built-in functions to match and fetch value if a cell contains text or string. In many cases, we try to search for a text or string in a range in Excel. Particular functions in Excel return value if cells contain specific/exact text or string. In this article, we will learn how to check if a cell contains text and then return the valuein Excel.

How to Check If Cell Contains Text Then Return Value in Excel: 9 Ways

In a situation, suppose we have entries of certain Categories and Products in a dataset like the image below. Now, using this dataset we will show you how to check if a cell contains text and then return the value in Excel.

1. Use IF Function to Check If Cell Contains Text Then Return Value in Excel

In the first method, we will use the IF function to check for text value in a cell. The syntax of the IF function is:

It results in one pre-selected value either [value_if_true] or [value_if_false] depending on the logical_test output; true or false respectively.

Steps:

• Firstly, select Cell D5 and insert the following formula.

• Then, press Enter and drag the Fill Handle to copy the formula for the rest of the cells.

• Now, Available or Not Available values will appear throughout the range.

2. Utilize IF & ISTEXT Functions to Check If Cell Contains Text Then Return Value

Next, we will use the IF and ISTEXT functions to check if a cell contains text or not. Follow the steps to do it on your own.

Steps:

• In the beginning, insert the following formula in Cell D5 and press Enter.

• After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Thus, the formula will return only the text values.

3. Apply ISNUMBER & SEARCH Functions to Find If Cell Contains Text

You can also apply the ISNUMBER & SEARCH Function to find if a cell contains text. The ISNUMBER function returns true or false depending on a match of SEARCH text in an absolute range.

Steps:

• Firstly, click on Cell D5 and insert the following formula.

• Next, press ENTER.
• Thus, True or False return value shows up for the total cell range.

4. Check If Cell Contains Text Then Return Value in Excel Using IF & EXACT Functions

If we consider the text as case-sensitive and want an exact match, we can use the EXACT function combined with the IF function.

Steps:

• In the beginning, select Cell D5 and paste the following formula.

• After that, hit ENTER, restaurant value will appear.
• Then, drag the Fill Handle to copy the formula.

• Now, the rest of the cells get the “Available” value or remain Blank.

5. Combine IF & COUNTIF Functions to Check If Cell Contains Text

Combined IF and COUNTIF function returns the same cell text as it matches criteria in a range.

Steps:

• To start with, insert the following formula in Cell D5 and press Enter.

• After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Now, matching cells will show the same values as the range.

6. Use INDEX and MATCH Functions to Find If Cell Contains Text Then Return Value in Excel

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 result in the value in another cell. To achieve this purpose, we slightly alter the dataset.

Steps:

• Firstly, insert the following formula in Cell C15.

• Then, press Enter if you are using Excel 365, otherwise press Ctrl + Shift + Enter, as it is an array formula.
• Thus, the matched text for Bars will appear.

7. Apply VLOOKUP Function to Check If Cell Contains Text in Excel

The VLOOKUP function is efficient to find vertical data in a table. In our case, we can use the VLOOKUP function to find an exact or approximate match in a column. The syntax of the VLOOKUP function is:

Steps:

• In the beginning, type the lookup text (Bars) in any cell (B16).

• After that, select Cell C16 and insert the following formula.

• Finally, press Enter and the matched value will appear.

8. Use Combined Functions to Check if Cell Contains Text with OR Condition

A dataset often contains more than one text string. We want to match the cells that have only one match text string. We can use ISNUMBER and SEARCH to match a text, then OR function to declare an alternative match. At last, IF functions to show a return value otherwise the cells remain Blank.

Steps:

• To start with, insert the following formula in Cell D5 and press Enter.

• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

• Now, the rest of the cells in the range get marked either by “Available” text or remaining Blank.

9. Check If Cell Contains Text with AND Condition Applying Combined Functions

From Method 8, we know how multiple text strings in a cell get matched. If we go further to match both text strings, we can use AND function instead of OR.

Steps:

• Firstly, select Cell D5 and insert the following formula.

• Then, press Enter. If both of the text strings exist in cell B5, the formula returns “Available” as a value otherwise the cells remain BLANK.
• Next, drag the Fill Handle to copy the formula for the rest of the cells.

• Finally, the rest of the cells get marked either “Available ” or remaining Blank.

Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Download Practice Workbook

Conclusion

In this article, we use various formulas to return values if cells contain certain texts. We use IF, ISNUMBER, EXACT, INDEX, MATCH, OR, and AND functions to return value for a text’s exact or approximate match. We also show methods to match more than one string by combining IF, AND, ISNUMBER, and SEARCH functions. Hope you find the discussed methods super easy to follow. Comment, if you need further clarifications or have something to add.

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