How to find text in a cell

Suppose you want to make sure a column only includes text and not numbers. There are a number of methods to determine if a cell contains words if you don't care about capitalization or punctuation.

Example of find text in a cell:

Does cell A1 contain egg? This Excel task is quite challenging. In this article, we examine a number of possibilities using the search, find, innumerate, and countif methods.Question: What formula tells you if A1 contains the text egg?

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

=FIND(egg,A1)

Add the IF function next if you want a TRUE/FALSE result:

=IF(FIND(egg,A1),TRUE)

If egg is located, this works well - FIND produces a number to indicate the location, and IF declares it successful and returns TRUE.

FIND, however, has a bothersome quirk: if egg is not found, it returns the #VALUE error. This implies that when text cannot be discovered, the formula above returns #VALUE rather than FALSE.

If the text is located, FIND returns its location; if it is not, it returns #VALUE.

Unfortunately, even if we wrap the FIND function with the IF function, this problem still occurs.

Nobody enjoys finding mistakes in their spreadsheets.

(Returning zero would be nicer, but there could be a true reason for this.)

What about the SEARCH feature, which finds the location of text as well?

SEARCH is case-insensitive and enables wildcards, in contrast to FIND. If the text cannot be discovered, perhaps SEARCH returns FALSE or zero?

Additionally, SEARCH returns #VALUE if the text cannot be located.

So what do you do? Therefore, using the ISNUMBER function, you may catch the #VALUE mistake like in the following example:

=ISNUMBER(FIND(egg,A1))

Now, when FIND returns a number, ISNUMBER returns TRUE, and when FIND returns an error, it returns FALSE.

ANOTHER COUNTIF method to find text in a cell:

You may also use the COUNTIF function to locate text if all of that seems a bit crazy:

=COUNTIF(A1,*egg*)

Given that we are just counting one cell, it may appear weird to utilise COUNTIF in this manner. However, COUNTIF does a good job; if egg is discovered, it returns 1, otherwise, it returns 0.

A 1 or 0 result will suffice in many circumstances (such as conditional formatting). However, you can simply wrap with IF if you wish to compel a TRUE/FALSE outcome.

=IF(COUNTIF(A1,*egg*),TRUE)

Now we get TRUE if egg is located and FALSE otherwise:

In reality, you must use wildcards to acquire the contains behaviour by adding an asterisk to either side of the text you're looking for. Take note that COUNTIF supports wildcards. Unfortunately, COUNTIF doesn't care about case, so if it does, you'll need to use FIND to find text in a cell.

So, Did you learn about how to find text in a cell in an Excel file? 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 the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience