If then statement to search for text possible?
 

=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!

If then statement to search for text possible?
 

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 then statement to search for text possible?
 

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!

If then statement to search for text possible?
 

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!

If then statement to search for text possible?
 

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!

If then statement to search for text possible?
 

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

If then statement to search for text possible?
 

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