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

`=IF(B5="Bars","Available","Not Available")`

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

`=IF(ISTEXT(B5),B5,"")`

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

`=ISNUMBER(SEARCH("Bars",B5:B5))`

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

`=IF(B5="Bars","Available","")`

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

`=IF(EXACT(B5,"Bars"),"Available","")`

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

`=IF(B5="Crackers",D5+1, D5 )`

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

`=IF(COUNTIF(B5,"Cra*"),"Yes","No")`

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

`=INDEX(C5:C13,MATCH("Bars",B5:B13,0))`

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

`=IF(ISERROR(FIND("Bars",B5,1)),"False","TRUE")`

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

`=$B5=$C$15`

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

`=FILTER(FILTER!B5:C13,FILTER!B5:B13="Crackers","")`

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

`=IF(OR(ISNUMBER(SEARCH("Bars",B5)),ISNUMBER(SEARCH("Veg",B5))),"Available","")`

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

**ISNUMBER**and

**SEARCH**functions to match the text “

**Bars’’**or “

**Veg**”. Then

**the OR function**is used to declare an alternative match. The

**IF**function shows the value if the condition meets. Otherwise, the cells remain blank.

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

`=IF(AND(ISNUMBER(SEARCH("Bars",B5)),ISNUMBER(SEARCH("Chlt",B5))),"Available ","")`

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

`=IF(B5="Bars","","No")`

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