[excel] 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.

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]

I like Rink.Attendant.6 answer. I actually want to check for multiple strings and did it this way:

First the situation: Names that can be home builders or community names and I need to bucket the builders as one group. To do this I am looking for the word "builder" or "construction", etc. So -

=IF(OR(COUNTIF(A1,"*builder*"),COUNTIF(A1,"*builder*")),"Builder","Community")

Here is the formula I'm using

=IF( ISNUMBER(FIND(".",A1)), LEN(A1) - FIND(".",A1), 0 )

This is an old question but a solution for those using Excel 2016 or newer is you can remove the need for nested if structures by using the new IFS( condition1, return1 [,condition2, return2] ...) conditional.

I have formatted it to make it visually clearer on how to use it for the case of this question:

=IFS(
ISERROR(SEARCH("String1",A1))=FALSE,"Something1",
ISERROR(SEARCH("String2",A1))=FALSE,"Something2",
ISERROR(SEARCH("String3",A1))=FALSE,"Something3"
)

Since SEARCH returns an error if a string is not found I wrapped it with an ISERROR(...)=FALSE to check for truth and then return the value wanted. It would be great if SEARCH returned 0 instead of an error for readability, but thats just how it works unfortunately.

Another note of importance is that IFS will return the match that it finds first and thus ordering is important. For example if my strings were Surf, Surfing, Surfs as String1,String2,String3 above and my cells string was Surfing it would match on the first term instead of the second because of the substring being Surf. Thus common denominators need to be last in the list. My IFS would need to be ordered Surfing, Surfs, Surf to work correctly (swapping Surfing and Surfs would also work in this simple example), but Surf would need to be last.

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.

It's an old question but I think it is still valid.

Since there is no CONTAINS function, why not declare it in VBA? The code below uses the VBA Instr function, which looks for a substring in a string. It returns 0 when the string is not found.

Public Function CONTAINS(TextString As String, SubString As String) As Integer
    CONTAINS = InStr(1, TextString, SubString)
End Function

Check out the FIND() function in Excel.

Syntax:

FIND( substring, string, [start_position])

Returns #VALUE! if it doesn't find the substring.

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")