When working with Excel, we see so many peculiar situations. One of those situations is searching for the particular text in the cell. The first thing that comes to mind when we say we want to search for a specific text in the worksheet is the “Find and Replace” method in Excel, which is the most popular one. But Ctrl + F can find the text you are looking for but cannot go beyond that. So, for example, if the cell contains certain words, you may want the result in the next cell as “TRUE” or “FALSE.” So, Ctrl + F stops there.

Find Text by FIND Function

1. This is a surprisingly tricky problem in Excel. The obvious answer is to use the FIND function to look for the text, like this:

=IF(FIND(apple,A1),TRUE)2.   

2. Then, if you want a TRUE/FALSE result, add the IF function.

3. This works great if apple is found – FIND returns a number to indicate the position, and IF calls it good and returns TRUE.

4. But FIND has an annoying quirk – if it doesn't find apple, it returns the #VALUE error.  This means that the formula above doesn't return FALSE when text isn't found, it returns #VALUE:

5. FIND returns the position of the text (if found), but #VALUE if not found.

=ISNUMBER(FIND(apple,A1))
6. Unfortunately, this error appears even if we wrap the FIND function in the IF function. So, what to do? Well, in a classic, counter-intuitive Excel move, you can trap the #VALUE error with the ISNUMBER function, like this:

Now ISNUMBER returns TRUE when FIND yields a number, and FALSE when FIND throws the error.

Search Text with COUNTIF

1. If all that seems a little crazy, you can also use the COUNTIF function to find text:

2. It might seem strange to use COUNTIF like this since we're just counting one cell. But COUNTIF does the job well – if apple is found, it returns 1, if not, it returns zero.

Find Text with conditional Formatting

=IF(COUNTIF(A1,*apple*), TRUE)For many situations (e.g. conditional formatting) a 1 or 0 result will be just fine. But if you want to force a TRUE/FALSE result, just wrap it with IF:

Now we get TRUE if apple is found, FALSE if not:

Note that COUNTIF supports wildcards – in fact, you must use wildcards to get the contains behavior, by adding an asterisk to either side of the text you're looking for. On the downside, COUNTIF isn't case-sensitive, so you'll need to use FIND if the case is important.

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.

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

1. Therefore, the formula will be:

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

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

Did you learn about how to find text in a cell in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an enjoyable and easy working experience.