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.
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")
- Now, press the ENTER key.
- Finally, drag down to AutoFill 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: 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")
- Now, press the ENTER key.
- Finally, drag down to AutoFill 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: If Cell Contain Word Then Assign Value in Excel (4 Formulas)
Similar Readings
- How to Return Value If Cells Contain Certain Text from a List
- Find text in an Excel range & return cell reference (3 ways)
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 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. 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)
- 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 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.
