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 value in 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:

`=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.

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

**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.

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

🔎** 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**.

### 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))`

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

**SEARCH**function matches the text

**“Bars”**in an absolute range and then returns

**True**or

**False**depending on the match.

### 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.

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

**EXACT**function matches the exact text

**“Bars”**in cell

**B5**then returns the value

**“Available”**otherwise

**BLANK**the cell depending on an exact match.

### 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.

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

**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**.

### 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**.

`=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.

**INDEX**function looks for the exact match text

**“Bars”**from the range

**B5:B13**in the range

**C5:C13.**

### 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**).

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

**“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.

### 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.

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

🔎** 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**.

### 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.

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

🔎** 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.

**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**

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

CurrencyorAccounting Number Format.1. Manually delete the preceding

Apostrophes. Or2. Use a formula to auto remove the

Apostrophesas shown in the image.`=NUMBERVALUE(SUBSTITUTE(B2,"'",""))`

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

Regards

Maruf Islam(Exceldemy Team)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 B2ofSheet 3in your Excel worksheet.`=TRIM(Sheet1!B2&" "&Sheet2!B2)`

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

Regards,

Arin Islam.

Hello, is it possible to somehow combine the EXACT and IF & ISTEXT Functions to return the text value of two cells in different columns instead of TRUE/FALSE? I am trying to match answers to two slightly different questions in a survey which have the same list of potential answers.

Hello

JAMESThanks for reaching out and sharing your queries. To return the text value of two cells in different columns if they match, you can combine the

EXACT,IFandISTEXTfunctions.Excel Formula:`=IF(AND(ISTEXT(A2), ISTEXT(B2), EXACT(A2, B2)), A2:B2, "No Match")`

So, in simpler terms, the formula checks if both cells A2 and B2 contain text and if the text in both cells is the same. If they are, it returns the values from cells A2 and B2 together. If not, it returns “No Match”.

OUTPUT:Hopefully, the idea will help you; good luck.

Regards

Lutfor Rahman ShimantoExcelDemy