Check whether a cell contains a substring

Question

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.

Answer 1

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

Answer 2

Check out the FIND() function in Excel.

Syntax:

FIND( substring, string, [start_position])

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

Answer 3

Try using this:

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

This will return TRUE if cell A3 contains Some Text.

Answer 4

Here is the formula I'm using

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

Answer 5

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

Answer 6

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]