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!

=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" wrote in message
...
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!

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..................



"Ken Wright" wrote in message
...
=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" wrote in message
...
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!

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

Ken Wright wrote:

=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" wrote in message
...
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!

--

Dave Peterson

Think I was braindead :-(

Cheers Dave

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

"Dave Peterson" wrote in message
...
I bet you meant:
=if(countif(a3,"*hou*"),"houston",0)

Ken Wright wrote:

=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" wrote in message
...
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!

--

Dave Peterson

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)

--
Regards

Roger Govier


"Steve" wrote in message
...
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!

Try...

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

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

Hope this helps!

In article ,
"Steve" wrote:

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!