Don't want #VALUE! in cells

[Roger Morris]

Mac OS 10.4.2 - Excel 11.2

I have a worksheet in which col B contains text.

I want to put "int" in col E on every row in which col B contains the
word "interest". (Could be "Interest added" or "gross interest" or a
number of other possibilities, ie the word is not in a predefined
position in the text)

Further, if "interest" does not occur in B, I want to put something else
from another IF test in the false section ("x" below) in col E.

this does work and can be replicated down:
=IF(SEARCH("*interest*",B1)>0,"INT","x")

BUT if "interest" doen not occur the formula result is #VALUE! which
precludes any further test and any alternative result in E.

I would rather that SEARCH gave a 0 (zero) answer if the searched-for
text was not found. Can this be done. Please help.

 

[Domenic]

Try...

=IF(ISNUMBER(SEARCH("interest",B1)),"INT","x")

Replace "x" accordingly.

Hope this helps!

 

[JE McGimpsey]

this does work and can be replicated down:
=IF(SEARCH("*interest*",B1)>0,"INT","x")

BUT if "interest" doen not occur the formula result is #VALUE! which
precludes any further test and any alternative result in E.

I would rather that SEARCH gave a 0 (zero) answer if the searched-for
text was not found. Can this be done. Please help.

One way:

=IF(ISERR(SEARCH("interest",B1)),"x","INT")

(note that there's no need for the *s)

An alternative:

=IF(COUNTIF(B1,"*Interest*"),"INT","x")

 

[Roger Morris]

One way:

=IF(ISERR(SEARCH("interest",B1)),"x","INT")

(note that there's no need for the *s)

An alternative:

=IF(COUNTIF(B1,"*Interest*"),"INT","x")

Thank you JE - I now have three solutions to choose from.

How do I train myself to do this lateral thinking more effectively

(I know - ask in another group!!!)

 

[Roger Morris]

Thank you Domenic

That's a cunning solution!

Try...

=IF(ISNUMBER(SEARCH("interest",B1)),"INT","x")

Replace "x" accordingly.

Hope this helps!