Mahbubur Rahman
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.
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")
- Now, press the ENTER key.
- Finally, drag down to AutoFill the rest of the series.
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", "")
- After that, press the ENTER key.
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")
- Now, press the ENTER key.
- Finally, drag down to AutoFill the rest of the series.
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
- How to Highlight Cell If Contains Text in Excel
- How to Return Value in Excel If Cell Contains Text from List
- How to Use IF-THEN Statements with Text in Excel
- How to Use “Not Equal to” Operator for Text in Excel
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.
- Finally, drag down to AutoFill the rest of the series.
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)
- 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.
- Now, press the ENTER key.
- 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.
