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.
How to Find Text in Cell in Excel: 2 Suitable Examples
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
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: How to Check If Cell Contains Specific Text in Excel
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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.
Related Articles
- How to Find Character in Excel String
- How to Find from Right in Excel
- How to Find Character in String from Right in Excel
- Excel Find Last Occurrence of Character in String
- How to Find * Character Not as Wildcard in Excel
<< Go Back to Find in String | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
