To explain detecting and adding text to cells, let’s use a sample dataset containing ID, Product Name, and Size.

### Method 1 – If Cell Contains Text Then Add Text in Another Cell Using ISTEXT Function

**Steps:**

- Type the following formula in cell
**E5:**

`=IF(ISTEXT(C5), "Correct", "Incorrect")`

- Press the
**Enter**key.

- Drag down the fill handle to
**AutoFill**the rest of the series.

ISTEXT(reference) checks whether the referenced cell has a text-only value, then the IF function returns “Correct” or “Incorrect” based on the result of the check.

### Method 2 – If Cell Contains Specific Text Then Add Text in Another Cell Using IF Function

Suppose you want to look for a text value “shirt” in the **Product Name **column.

**Steps:**

- Type the following formula in cell E5:

`=IF(C5="Shirt", "Yes", "")`

- Press
**Enter**.

You will get **Yes **as a return value every time a cell contains **Shirt **as a text value or a **blank **cell otherwise.

**Drag down**the fill handle to see the result in the rest of the cells.

### Method 3 – Using Search Along with ISNUMBER Function

**Steps:**

- Type the following formula in cell
**E5:**

`=IF(ISNUMBER(C5), "", "Text")`

- Hit
**Enter**to apply.

- Drag down the fill handle to
**AutoFill the**rest of the series.

**ISNUMBER **returns the value as **TRUE **or **FALSE **depending on whether the cell contains only a number. Using the **IF **function we’re telling **Excel **to return the **TRUE **as a **blank **cell and **FALSE **as **Text**.

### Method 4 – If Cell Contains Many Texts then Add Text in Another Cell Using Combined Formula

**Steps:**

- Type the following formula in cell
**E5:**

`=IF(AND(ISNUMBER(SEARCH("Shirt",C5)), ISNUMBER(SEARCH("Black",C5))), "Yes","")`

- Press the
**Enter**key.

- Drag down the fill handle to the other cells in the E column.

For the example, **ISNUMBER(SEARCH(“Shirt”,C5))** gives the output **TRUE**, **ISNUMBER(SEARCH(“Black”,C5) **also gives **TRUE **as output. **AND **function will count the value as **TRUE **or If both the output is **TRUE **otherwise False. The **SEARCH** function returns the value as a number.

### Method 5 – Using VLOOKUP Function to Add Text in Another Cell

Let’s look for a specific value in our **Product Name** containing text, and then return the Size associated with it.

**Steps:**

- First, type the following formula in cell
**E5**.

`=VLOOKUP(F6,C5:D11,2,FALSE)`

- Press the
**Enter**key.

With **VLOOKUP, **we are looking for the text **Jeans (in cell F6) **in the data range **C5:D11 **and want the return value from the **Size **column which is associated with it, which is why we used **2 **as the column index number.

### Method 6 – IF with COUNTIF Function to Add Text If Cell Contains Text

Let’s look for cells that have **Black **as a text in them and want to return the full text in another column.

**Steps:**

- Type the following formula in cell
**E5:**

**=IF(COUNTIF(C5, “*”&”Black”&”*”), C5, “”)**

- Press the
**Enter**key.

- Drag down the fill handle to
**AutoFill**the rest of the cells.

**COUNTIF(C5, “*”&”Black”&”*”) **will return the value **1** if **Black **text exist in cell **C5**. Then **=IF(1, C5, “”)** will give output as it is in **C5 **which is **Shirt, Black, Small**.

## Practice Section

Weâ€™ve attached a practice workbook where you may practice these methods.

**Download Practice Workbook**

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