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


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.


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.


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.


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.


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.


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.


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.


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

Get FREE Advanced Excel Exercises with Solutions!
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo