Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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 use IF, ISNUMBER, EXACT, COUNTIF, INDEX, MATCH, VLOOKUP, SEARCH, OR, and AND functions to return value if cell contains text or string in Excel.


Download Practice Workbook

You can download the workbook to practice yourself.


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

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.

Dataset for Checking If Cell Contains Text Then Return 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:

=IF (logical_test,[value_if_true],[value_if_false])

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.
=IF(B5="Bars","Available","Not Available")
  • Then, press Enter and drag the Fill Handle to copy the formula for the rest of the cells.

Using IF Function to Check If Cell Contains Text Then Return Value in Excel

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

Values Found After Checking Cells Contains Text Using IF Function

Here, the logical_test is to match Bars text in cell B5; if the test is TRUE it results in Available, otherwise Not Available.

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.
=IF(ISTEXT(B5),B5,"")
  • After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Utilizing IF & ISTEXT Functions to Check If Cell Contains Text Then Return Value in Excel

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

Values Found After Checking If Cells Contains Text Utilizing IF & ISTEXT Functions

🔎 How Does the Formula Work?

  • To start with, we used the ISTEXT function, to check if Cell B5 contains a text or not.
  • Then, we used the IF function to return the value of Cell B5 if the result of the ISTEXT function is TRUE, otherwise return a Blank.

Related Content: How to Sum If Cell Contains Specific Text in Excel (6 Ways)


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.
=ISNUMBER(SEARCH("Bars",$B$5:$B$13))

Applying ISNUMBER & SEARCH Functions to Find If Cell Contains Text Then Return Value in Excel

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

Values Found After Checking If Cells Contains Text Applying ISNUMBER & SEARCH Functions

In the formula, the SEARCH function matches the text “Bars” in an absolute range then returns True or False depending on the match.

Read More: If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)


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.
=IF(EXACT(B5,"Bars"),"Available","")
  • After that, hit ENTER, restaurant value will appear.
  • Then, drag the Fill Handle to copy the formula.

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

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

Values Found After Checking Cells Contains Text Using IF & EXACT Functions

Inside the formula, the EXACT function matches the exact text “Bars” in cell B5 then returns the value “Available” otherwise BLANK the cell depending on an exact match.

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


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.
=IF(COUNTIF(B5,"*Bars*"),B5,"")
  • After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Combining IF & COUNTIF Functions to Check If Cell Contains Text Then Return Value in Excel

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

Values Found After Checking Cells Contains Text Using IF & COUNTIF Functions

In the formula, the COUNTIF function matches the criteria “*Bars*” (the formula automatically puts * both sides of the criteria) in range (cell B5). Then it returns the value in B5 otherwise keeps the cell Blank.

Read More: If Cell Contains Text Then Add Text in Another Cell in Excel


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 the purpose, we slightly alter the dataset.

Steps:

  • Firstly, insert the following formula in Cell C15.
=INDEX(C5:C13,MATCH("Bars",B5:B13,0))
  • 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.

Using INDEX and MATCH Functions to Find If Cell Contains Text Then Return Value in Excel

Here, the INDEX function looks for the exact match text “Bars” from the range B5:B13 in the range C5:C13.

Read More: How to Return Value If Cells Contain Certain Text from a List


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:

=VLOOKUP (value,table,col_index,[range_lookup])

Steps:

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

Applying VLOOKUP Function to Check If Cell Contains Text in Excel

  • After that, select Cell C16 and insert the following formula.
=VLOOKUP(B16,B5:C13,2,FALSE)
  • Finally, press Enter and the matched value will appear.

Values Found After Checking Cells Contains Text Using VLOOKUP Function

Here “Bars” is the text in B3 that has to match within a range B7:C15 to a value in column 2. FALSE declares we want an exact match.

Read More: How to Use VLOOKUP If Cell Contains a Word within Text in Excel


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.
=IF(OR(ISNUMBER(SEARCH("Bars",B5)),ISNUMBER(SEARCH("Veg",B5))),"Available","")
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Using Combined Functions to Check if Cell Contains Text with OR Condition in Excel

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

Values Found After Checking Cells Contains Text Using Combined Functions with OR Condition

🔎 How Does the Formula Work?

  • In the beginning, we used the SEARCH function to find cell range Bars in Cell B5.
  • After that, we used the ISNUMBER function to check if the result of the SEARCH function is a number.
  • Similarly, we searched for Veg in Cell B5 using these two functions.
  • Then, we used the OR function to check if any of these two texts (Bars or Veg) is present in Cell B5.
  • Lastly, we used the IF function to return Available if True, otherwise Blank.

Read More: Check If Cell Contains Partial Text in Excel (5 Ways)


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.
=IF(AND(ISNUMBER(SEARCH("Bars",B5)),ISNUMBER(SEARCH("Chlt",B5))),"Available ","")
  • 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.

Checking If Cell Contains Text with AND Condition Applying Combined Functions in Excel

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

Values Found After Checking Cells Contains Text Using Combined Functions with AND Condition

🔎 How Does the Formula Work?

  • Here, we used the SEARCH and ISNUMBER functions like Method 8.
  • Then, we used the AND function to check if these two texts (Bars and Veg) are present in Cell B5.
  • Lastly, we used IF function to return Available if True, otherwise Blank.

Practice Section

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

Practice Section to Check Cell Contains Text Value Then Return Value in Excel


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 discussed methods super easy to follow. Comment, if you need further clarifications or have something to add. And visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

4 Comments
  1. Reply
    Dr. Linda Vandergriff Sep 29, 2022 at 2:08 AM

    If some idiot changes the dollar value by putting a text character in it, how do I get just the value? Not of these methods seem to help.

    Cell = ‘50.50 I want to get the value Cell 2 = 50.50 Where is this covered?

    • Greetings Dr. Linda Vandergriff,

      Go through the following methods to get rid of the inconvenience. Make sure the value cells are in the Currency or Accounting Number Format.

      1. Manually delete the preceding Apostrophes. Or

      2. Use a formula to auto remove the Apostrophes as shown in the image.

      =NUMBERVALUE(SUBSTITUTE(B2,"'",""))

      Formula

      Feel free to comment if you have further inquiries. We are here to help.

      Regards
      Maruf Islam (Exceldemy Team)

  2. HI, I want to pull text from multiple sheets and ignore some specific text as blank or space. How to do it?

    Example .
    If sheet 1 has John in B2 and sheet2 has Jacob in B2 and i want an output in sheet3, where if sheet 2 has Jacob then ignore or blank.
    =Sheet1!B2&” “&Sheet2!B2

    • Hi Mohammed Shahid,
      You can pull text from multiple sheets and ignore blank or space by using the TRIM function. This function will help you to remove space automatically.
      To solve your problem, use the following formula in Cell B2 of Sheet 3 in your Excel worksheet.
      =TRIM(Sheet1!B2&" "&Sheet2!B2)

      Pulling Text from Multiple Sheets and Removing Blank or Space

      Hope this will solve your problem. Feel free to comment if you have further inquiries.
      Regards,
      Arin Islam.

Leave a reply

ExcelDemy
Logo