If then statement to search for text possible?

[Ken Wright]

=IF(SEARCH("*HOU*",A3),"Houston",0)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Steve]

I was wondering if there is a way to write an if statement like this:

=if(a1="*HOU*", "Houston",0)

So that if the text in cell A1 contains "HOU" then it would return "Houston".

I know it works if the cell is an exact match, but what about if it only
contains the word???

HELP!

Thanks!

 

[Roger Govier]

Hi Steve

You could use
=IF(ISNUMBER(FIND("HOU",A1)),"Houston",0)
if you are looking for only HOU and not Hou or hou.
If you don't want to be case sensitive, then use Search instead.
=IF(ISNUMBER(SEARCH("HOU",A1)),"Houston",0)
or
=IF(ISNUMBER(MATCH("HOU",A1)),"Houston",0)

 

[Domenic]

Try...

=IF(ISNUMBER(SEARCH("HOU",A1)),"Houston",0)

If you want the formula to be case-sensitive, replace SEARCH with FIND.

Hope this helps!

 

[Ken Wright]

Oops - my bad. Forgot to trap for when no match. Both the others gave you
the right steer though.

=IF(ISNUMBER(SEARCH("*HOU*",A1)),"Houston",0)

Apologies again.

Regards
Ken..................

 

[Dave Peterson]

I bet you meant:
=if(countif(a3,"*hou*"),"houston",0)

 

[Ken Wright]

Think I was braindead :-(

Cheers Dave

Regards
Ken...............