If then statement to search for text possible?

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

[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" <Steve@discussions.microsoft.com> wrote in message
news:FB1699A5-7665-4779-8878-272568F68939@microsoft.com...
>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)

--
Regards

Roger Govier


"Steve" <Steve@discussions.microsoft.com> wrote in message
news:FB1699A5-7665-4779-8878-272568F68939@microsoft.com...
>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!

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

In article <FB1699A5-7665-4779-8878-272568F68939@microsoft.com>,
"Steve" <Steve@discussions.microsoft.com> 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!

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



"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OshBuBgJGHA.1088@tk2msftngp13.phx.gbl...
> =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" <Steve@discussions.microsoft.com> wrote in message
> news:FB1699A5-7665-4779-8878-272568F68939@microsoft.com...
>>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]

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" <Steve@discussions.microsoft.com> wrote in message
> news:FB1699A5-7665-4779-8878-272568F68939@microsoft.com...
> >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

[Ken Wright]

Think I was braindead :-(

Cheers Dave

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

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43DEB821.469AECEA@verizonXSPAM.net...
>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" <Steve@discussions.microsoft.com> wrote in message
>> news:FB1699A5-7665-4779-8878-272568F68939@microsoft.com...
>> >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