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

Get FREE Advanced Excel Exercises with Solutions!

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


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

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 the 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: Check If Cell Contains Text Then Return Value with Multiple Conditions in Excel


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, that 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 the 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: How to Assign Value If Cell Contains Word in Excel


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


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 the 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: How to Check If Cell Contains Text Then Return Value in Excel


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.


Download Practice Workbook


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

Mahbubur Rahman
Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo