Check whether a cell contains a substring

Is there an in-built function to check if a cell contains a given character/substring?

It would mean you can apply textual functions like Left/Right/Mid on a conditional basis without throwing errors when delimiting characters are absent.

Solution 1:

Try using this:

=ISNUMBER(SEARCH("Some Text", A3))

This will return TRUE if cell A3 contains Some Text.

Solution 2:

The following formula determines if the text "CHECK" appears in cell C10. If it does not, the result is blank. If it does, the result is the work "CHECK".

=IF(ISERROR(FIND("CHECK",C10,1)),"","CHECK")

Solution 3:

This formula seems more intuitive to me:

=SUBSTITUTE(A1,"SomeText","") <> A1

this returns TRUE if "SomeText" is contained within A1.

The IsNumber/Search and IsError/Find formulas mentioned in the other answers certainly do work, but I always find myself needing to look at the help or experimenting in Excel too often with those ones.

Solution 4:

For those who would like to do this using a single function inside the IF statement, I use

=IF(COUNTIF(A1,"*TEXT*"),TrueValue,FalseValue)

to see if the substring TEXT is in cell A1

[NOTE: TEXT needs to have asterisks around it]