If Cell Contains Text Then Add Text in Another Cell in Excel

While working in Excel, sometimes we feel the need to identify whether a cell contains TEXT or not. In this article, we see some simple and easy methods for Excel if cell contains text then add text in another cell. For your better understanding, we will use a sample dataset containing ID, Product Name, and Size.

excel if cell contains text then add text in another cell


Download Practice Workbook


6 Ways to Add Text in Another Cell in Excel If Cell Contains Text

Here, we will see the use of Functions like IF, ISTEXT, ISNUMBER, COUNTIF, VLOOKUP, etc.


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

In our first method, we will see, how to identify if a cell contains the text.

Steps:

  • First, type the following formula in cell E5.
=IF(ISTEXT(C5), "Correct", "Incorrect")

excel if cell contains text then add text in another cell using ISTEXT

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

excel if cell contains text then add text in another cell by ISTEXT Function

The ISTEXT function is a useful function to determine a cell value if text and using IF along with ISTEXT add an extra option to return the value if TRUE. As we used our command as Correct and Incorrect, the formula will yield correct in another cell if our specific cell has text value otherwise will return the value as Incorrect.

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


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

If we want to look for a specific text value in cells, we can use the IF function. Suppose, we want to look for a text value shirt in our Product Name column.

Steps:

  • First, type the following formula in cell E5.
=IF(C5="Shirt", "Yes", "")

excel if cell contains text then add text in another cell using IF

  • After that, press the ENTER key.

excel if cell contains text then add text in another cell using If function

Here, we will get Yes as a return value every time a cell contains Shirt as a text value, otherwise a blank cell.

  • Finally, drag down to see the result in the rest of the cells.

That’s it.

Read More: Excel Formula If Cell Contains Text Then Return Value in Another Cell


Method 3: Using Search Along with ISNUMBER Function

ISNUMBER is an in-built function in Excel to identify number values. We have a dataset called Product Name, so it is a category for a text value, what if it contains a number.

Steps:

  • First, type the following formula in cell E5.
=IF(ISNUMBER(C5), "", "Text")

excel if cell contains text then add text in another cell by ISNUMBER

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

excel if cell contains text then add text in another cell by ISNUMBER function

ISNUMBER returns the value as TRUE or FALSE depending on the cell containing numbers. Using the IF function we’re telling Excel to return the TRUE as a blank cell and False as Text.

Read More: If Cell Contain Word Then Assign Value in Excel (4 Formulas)


Similar Readings:


Method 4: If Cell Contains Many Texts Then Add Text in Another Cell Using Combined Formula

Our cells may contain more than one text, AND the function comes to the rescue in this case.

Steps:

  • First, type the following formula in cell E5.
=IF(AND(ISNUMBER(SEARCH("Shirt",C5)), ISNUMBER(SEARCH("Black",C5))), "Yes","")

  • Now, press the ENTER key.

excel if cell contains text then add text in another cell Using AND function

  • Finally, drag down to AutoFill rest of the series.

excel if cell contains text then add text in another cell by combination of IF,AND,ISNUMBER,SEARCH

What is happening here? 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. SEARCH function returns the value as a number. And how the IF function works you already know.

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


Method 5: Using VLOOKUP Function to Add Text in Another Cell

Now, we want to look for a specific value in our Product Name containing text and want a specific value associated with that cell. Let’s see, how to do it.

Steps:

  • First, type the following formula in cell E5.
=VLOOKUP(F6,C5:D11,2,FALSE)

excel if cell contains text then add text in another cell by VLOOKUP

  • Now, press the ENTER key.

Using VLOOKUP we are looking for the text Jeans in the data range C5:D11 and we want the return value from the Size column which is associated with Jeans, that’s why we used 2 as the column index number in our formula.

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


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

Here, we want to look for cells that have Black as a text in them and want to return the full text in another column.

Steps:

  • First, type the following formula in cell E5.
=IF(COUNTIF(C5, “*”&”Black”&”*”), C5, “”)

excel if cell contains text then add text in another cell USING countif

  • Now, press the ENTER key.

excel if cell contains text then add text in another cell countif function

  • Finally, drag down to AutoFill rest of the series.

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

Read More: Excel If Cell Contains Text Then Return Value (8 Easy Ways)


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.


Conclusion

That’s all for the article. These are 6 different methods for Excel If Cell Contains Text Then Add Text in Another Cell. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo