Rifat Hassan
While working in Excel, we often have to search for a specific text within another text. In Microsoft Excel, we can do such jobs in multiple ways. Today I will be showing how to find a text within another text in a cell in Excel with two suitable examples. If you are also curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
2 Suitable Examples to Find Text in Cell in Excel
To demonstrate the examples, we consider a dataset of 10 Email IDs of 10 people. We will find out whether the email domain belongs to Gmail or not. Our dataset is in the range of cells B5:B14, and we will show our result in the range of cells C5:C14, respectively.
All the operations of this article are accomplished by using Microsoft Office 365 application.
Example 1: Combine SEARCH, ISNUMBER, and IF Functions to Find Text in Cell
In the first example, we are going to use the SEARCH, ISNUMBER, and IF functions to find the text from a cell. This example is a case-insensitive problem. So, we will check whether the word Gmail is present in our entities. The steps to complete the process are given below:
📌 Steps:
- First of all, select cell C5.
- Now, write down the following formula in the cell.
=IF(ISNUMBER(SEARCH("Gmail",B5)),"Yes","No")
- Hence, press Enter.
- As the word Gmail is present in the data of cell B5, the formula returned Yes in cell C5.
- After that, drag the AutoFill Handle icon to copy the formula up to cell C14.
- Finally, you will see that our formula will show the result for all the data.
Thus, we can say that our formula works perfectly, and we are able to find text in a cell in Excel.
We are breaking down the formula for cell C5.
👉 SEARCH(“Gmail”,B5): The SEARCH function will search for our desired character and show the character number. Here, the function will return 12.
👉 ISNUMBER(SEARCH(“Gmail”,B5)): The ISNUMBER function will check whether the result of the SEARCH function is a number or not. If the result is a number it will return TRUE. Otherwise, it will show FALSE. Here, the function will return TRUE.
👉 IF(ISNUMBER(SEARCH(“Gmail”,B5)),”Yes”,”No”): Finally, the IF function checks the value of the ISNUMBER function is true or false. If the result is true the IF function will return Yes, On the other hand, it will return No. Here, the function will return Yes.
Read More: How to Find If Range of Cells Contains Specific Text in Excel
Similar Readings
- Excel Find Last Occurrence of Character in String (6 Methods)
- How to Find a Character in String in Excel (9 Suitable Examples)
- Find Multiple Values in Excel (8 Quick Methods)
- How to Find from Right in Excel (6 Methods)
- Find Last Value in Column Greater than Zero in Excel
Example 2: Merge FIND and ISNUMBER Functions to Find Text in Cell
In the following example, we will use the FIND, ISNUMBER, and IF functions to find the text from a cell. This is a case-sensitive problem. Because the FIND function will look for the exact same entity in our cells. Here, we will check for the exact word Gmail is present or not. The steps to finish this example are given as follows:
📌 Steps:
- At first of all, select cell C5.
- Afterward, write down the following formula in the cell.
=IF(ISNUMBER(FIND("Gmail",B4)),"Yes","No")
- Then, press Enter.
- Here, the exact word Gmail is absent in the text of cell B5, the formula returned No in cell C5.
- Now, drag the AutoFill Handle icon to copy the formula up to cell C14.
- You will see that our formula will show the result for all the data, and in real life, there is no domain with Gmail. Hence, all the results will be No.
Finally, we can say that our formula works successfully, and we are able to find text in a cell in Excel.
We are breaking down the formula for cell C5.
👉 FIND(“Gmail”,B4): The FIND function will check for the exact character and show the character number. As the word is not present in our text, the function will return a #VALUE error.
👉 ISNUMBER(FIND(“Gmail”,B4)): The ISNUMBER function will check whether the result of the FIND function is a number or not. If the result is a number it will return TRUE. In contrast, it will show FALSE. Here, the function will return FALSE.
👉 IF(ISNUMBER(FIND(“Gmail”,B4)),”Yes”,”No”): In the end, the IF function checks the value of the ISNUMBER function is true or false. If the result is true the IF function will return Yes, Otherwise, it will return No. Here, the function will return No.
Read More: Use ISNUMBER Function with IF and Then Statements in Excel
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to find text in a cell in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- FIND and SEARCH Function in Excel (2 Comparison Studies)
- How to Find External Links in Conditional Formatting in Excel (2 Ways)
- FIND Function Not Working in Excel (4 Reasons with Solutions)
- [Solved!] CTRL+F Not Working in Excel (5 Fixes)
- [Fixed]: Can’t Find Project or Library Error in Excel (3 Solutions)
- How to Find * Character Not as Wildcard in Excel (2 Methods)
