While working in Excel, we often have to search for a specific text within another text. Today I will be showing how to find a text within another text in a cell in Excel.
How to Find Text in Excel (Quick View)
Download Practice Workbook
How to Find Text in Cell in Excel
Here we’ve got a data set with the Email IDs of some regular customers of a company.
Our objective here is to try to decide whether the email addresses are Gmail or not.
To accomplish that, we shall look for the text “Gmail” within the Email IDs.
Case 1: Case-Insensitive Match (Using ISNUMBER and SEARCH Functions)
First of all, we shall look for case-insensitive matches.
In this case, the text “Gmail” will match the text “gmail” within the Email IDs.
Step 1: SEARCH Function
You can use the SEARCH function of Excel to search for any text within another text for a case-insensitive match.
It will return the position of the character where the match starts.
And in case of no match, it will return an error.
The syntax of the SEARCH function is:
So, in this case, the formula will be:
- Here, B4 is the cell with the first Email ID. The 12th character within it contains the text “gmail”.
- The Emails where there is no text “gmail”, return a #VALUE! error.
Step 2: ISNUMBER with SEARCH Function
Then you can wrap it within an ISNUMBER function to return a TRUE if that is a “gmail”, and return a FALSE if that is not a “gmail”.
Step 3: IF, ISNUMBER, and SEARCH Functions
Finally, you can wrap the whole function within an IF function to return a specific value (Let’s say “Yes”) if it is a gmail, and “No” if it is not.
Case 2: Case-Sensitive Match (Using ISNUMBER and FIND Functions)
To search for a case-sensitive match, you can use the same formula as mentioned above. Just replace the SEARCH function with the FIND function.
Therefore, the formula will be:
As this is a case-sensitive match, it will return “No” if you use “Gmail” within your formula.
But it will return “Yes” if you use “gmail” within your formula.
Using these methods, you can look for a specific text within another text in Excel. You can look for both case-sensitive and case-insensitive matches. Do you have any questions? Feel free to ask us.