How to Find Text in Cell in Excel

ISNUMBER and SEARCH Function to Find Text in Excel

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)

Quick View to Find Text in a cell in Excel


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.

Data Set to Find Text in Cell in Excel

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:

=SEARCH(find_text,within_text)

So, in this case, the formula will be:

=SEARCH("Gmail",B4)

SEARCH Function to Find Text in Cell in Excel

  • 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”.

=ISNUMBER(SEARCH("Gmail",B4))

ISNUMBER and SEARCH Function to Find Text in Excel

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.

=IF(ISNUMBER(SEARCH("Gmail",B4)),"Yes","No")

IF, ISNUMBER and SEARCH Function to Find Text in Cell in Excel

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:

=IF(ISNUMBER(FIND("Gmail",B4)),"Yes","No")

As this is a case-sensitive match, it will return “No” if you use “Gmail” within your formula.

FIND Function to Find Text in Cell in Excel

But it will return “Yes” if you use “gmail” within your formula.

FIND Function to Find Text in Cell in Excel


Conclusion

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo