If Cell Contains Text Then Add Text in Another Cell in Excel: 6 Ways

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:

• 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:

• 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:

• 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:

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

• 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:

• 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