IF a cell contains a string

Question

How can I assign a value to cells if it's neighbour contains a specific string?

For example, fields in column A:

    dog11
    cat22
    cow11
    chick11
    duck22
    cat11
    horse22
    cat33

The syntax in column B would be:

=IF(SEARCH("cat",A1),"cat",IF(SEARCH("22",A1),"22","none"))

It always picks up the first TRUE cell, but drops when the value is not true.

Clint

Answer 1

SEARCH does not return 0 if there is no match, it returns #VALUE!. So you have to wrap calls to SEARCH with IFERROR.

For example...

=IF(IFERROR(SEARCH("cat", A1), 0), "cat", "none")

or

=IF(IFERROR(SEARCH("cat",A1),0),"cat",IF(IFERROR(SEARCH("22",A1),0),"22","none"))

Here, IFERROR returns the value from SEARCH when it works; the given value of 0 otherwise.

Interested

Thank you for the answer. Can't believe MS has not do a short method to handle this, such as "CONTAINS" which returns directly true or false, or "INDEX" which returns "-1" or the actual position... – Kerge

Answer 2

You can use OR() to group expressions (as well as AND()):

=IF(OR(condition1, condition2), true, false)

=IF(AND(condition1, condition2), true, false)

So if you wanted to test for "cat" and "22":

=IF(AND(SEARCH("cat",a1),SEARCH("22",a1)),"cat and 22","none")

Mehitable

Hey, thanks for that, but i'm hoping to get two different results here. when a string contains "cat" I wanna say "cat" when it contains "22" I wanna say 22. In the real world example there will be no overlap. thanks – Clint

And as long as the value has one, it will continue to short-circuit, unless you concatenate. e.g. =Concatenate(IF(SEARCH("cat",a1),"cat",""),IF(SEARCH("22",a1),"22","")) – Mehitable

Answer 3

=IFS(COUNTIF(A1,"*cats*"),"cats",COUNTIF(A1,"*22*"),"22",TRUE,"none")

Advanced

Can you explain why your answer is better than the one that is already accepted? – Greenlee