How To Find Text With A Formula

How To Find Text With A Formula

What formula will tell you that a specific cell contains a specific text? Well, you need to make use of FIND function in order to search for some particluar text.

FIND (text, cell)

FIND(“Bat”, C3)

If you want a false or true result, the formula goes like

IF(FIND("Bat",C3),TRUE)

The function is going to work perfectly and returns the position. However, if the text it not found, it will return an error value.  

 

SEARCH:

There is another function in Excel known as SEARCH function. It is also used to get the location of text. SEARCH is not case-sensitive as FIND is and supports wildcards. One thing is that the SEARCH function will also return some value if the text is not found.

 

ISFUNCTION:

 

So, what is the solution now? No need to worry. There is a function named ISFUNCTION. The function returns true in case the text is find and returns false when it is not without any error.

 

COUNTIF:

The use of COUNTIF may sound awkward at the start but it will surely work for you. It is as simple as anything. You can make use of this function as you are only counting a single cell. It is surely going to work and will return 1 if text is found and 0 if not.

IF ( COUNTIF (C3 ,"*Bat* ") ,TRUE )

 

One thing to be noted here is that COUNTIF is supporting the wildcards. The fact is that you must make use of the wildcards if you want the contain behavior. Add an asterisk on both sides and you will get the result. Another thing that should be notes is that COUNTIF is not case sensitive. Thus, if you are looking for some case sensitive results, it may not be the good choice for you.

 

Related Trainings

Body Mass Index Calculation

Body Mass Index Calculation

Create a body mass index calculator using height and weight of a person


New Text Functions

New Text Functions

Combine text strings using TEXTJOIN() and CONCAT().


Practice: Date Functions

Practice: Date Functions

Making more practice by using date functions.