Method 1 – Combine SEARCH, ISNUMBER, and IF Functions to Find Text in Cell
Steps:
- Select cell C5.
- Write down the following formula in the cell.
=IF(ISNUMBER(SEARCH("Gmail",B5)),"Yes","No")
- Press Enter.
- As the word Gmail is present in the data of cell B5, the formula returned Yes in cell C5.
- Drag the AutoFill Handle icon to copy the formula up to cell C14.
- See that our formula will show the result for all the data.
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. It will show FALSE. The function will return TRUE.
IF(ISNUMBER(SEARCH(“Gmail”,B5)),”Yes”,”No”): 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. The function will return Yes.
Method 2 – Merge FIND and ISNUMBER Functions to Find Text in Cell
Steps:
- Select cell C5.
- Write down the following formula in the cell.
=IF(ISNUMBER(FIND("Gmail",B4)),"Yes","No")
- Press Enter.
- The exact word Gmail is absent in the text of cell B5; the formula returned No in cell C5.
- 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. There is no domain with Gmail, all the results will be No.
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. The function will return a #VALUE error if the word is absent in our text.
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. 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. It will return No. The function will return No.
Download Practice Workbook
Download this practice workbook while you are reading this article.
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!
