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

Expense Sheet

Expense Sheet

Using Nested IF.


Privileged Customers

Privileged Customers

Matching nested IF() and IFS() functions.


Vacation Day Calculator

Vacation Day Calculator

Calculate vacation days based on years worked and other conditions