=IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

Is it possible to add additional words to a statement of this nature?

Examples:

coverage
homeowners
annuity
blue cross
group

Thanks!

cynichromantique

Yes!! What is it you want the formula to do? You'll have to use an AND or an
OR.
Post back with what you want.

Andy.

"cynichromantique" <[email protected]> wrote in
message news:[email protected]...
> Is it possible to add additional words to a statement of this nature?
>
> Examples:
>
> coverage
> homeowners
> annuity
> blue cross
> group
>
> Thanks!
>
> cynichromantique
>

I have a spreadsheet with over 30000 items listed in column A. I would like
to be able to find specific column "A" cells with specific words and place an
asterick in column "I" to designate that. So if column "A" contains any of
the words I have listed, I want to place an asterick in the corresponding "I"
cell.

Thanks!

"Andy" wrote:

> Yes!! What is it you want the formula to do? You'll have to use an AND or an
> OR.
> Post back with what you want.
>
> Andy.
>
> "cynichromantique" <[email protected]> wrote in
> message news:[email protected]...
> > Is it possible to add additional words to a statement of this nature?
> >
> > Examples:
> >
> > coverage
> > homeowners
> > annuity
> > blue cross
> > group
> >
> > Thanks!
> >
> > cynichromantique
> >
>
>
>

Hi

You'll have to put each option into an AND statement:

=IF(AND(ISERROR(SEARCH("insurance",A125,1))),ISERROR(SEARCH("coverage",a125,1)),ISERROR(SEARCH("homeowners",a125,1)),ISERROR(SEARCH("annuity",a125,1)),ISERROR(SEARCH("bluecross",a125,1)),ISERROR(SEARCH("group",a125,1))),"","*")

This all goes into one cell, on one line.

Andy.




"cynichromantique" <[email protected]> wrote in
message news:[email protected]...
>I have a spreadsheet with over 30000 items listed in column A. I would
>like
> to be able to find specific column "A" cells with specific words and place
> an
> asterick in column "I" to designate that. So if column "A" contains any
> of
> the words I have listed, I want to place an asterick in the corresponding
> "I"
> cell.
>
> Thanks!
>
> "Andy" wrote:
>
>> Yes!! What is it you want the formula to do? You'll have to use an AND or
>> an
>> OR.
>> Post back with what you want.
>>
>> Andy.
>>
>> "cynichromantique" <[email protected]> wrote in
>> message news:[email protected]...
>> > Is it possible to add additional words to a statement of this nature?
>> >
>> > Examples:
>> >
>> > coverage
>> > homeowners
>> > annuity
>> > blue cross
>> > group
>> >
>> > Thanks!
>> >
>> > cynichromantique
>> >
>>
>>
>>

Thanks!!!!!

"Andy" wrote:

> Hi
>
> You'll have to put each option into an AND statement:
>
> =IF(AND(ISERROR(SEARCH("insurance",A125,1))),ISERROR(SEARCH("coverage",a125,1)),ISERROR(SEARCH("homeowners",a125,1)),ISERROR(SEARCH("annuity",a125,1)),ISERROR(SEARCH("bluecross",a125,1)),ISERROR(SEARCH("group",a125,1))),"","*")
>
> This all goes into one cell, on one line.
>
> Andy.
>
>
>
>
> "cynichromantique" <[email protected]> wrote in
> message news:[email protected]...
> >I have a spreadsheet with over 30000 items listed in column A. I would
> >like
> > to be able to find specific column "A" cells with specific words and place
> > an
> > asterick in column "I" to designate that. So if column "A" contains any
> > of
> > the words I have listed, I want to place an asterick in the corresponding
> > "I"
> > cell.
> >
> > Thanks!
> >
> > "Andy" wrote:
> >
> >> Yes!! What is it you want the formula to do? You'll have to use an AND or
> >> an
> >> OR.
> >> Post back with what you want.
> >>
> >> Andy.
> >>
> >> "cynichromantique" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > Is it possible to add additional words to a statement of this nature?
> >> >
> >> > Examples:
> >> >
> >> > coverage
> >> > homeowners
> >> > annuity
> >> > blue cross
> >> > group
> >> >
> >> > Thanks!
> >> >
> >> > cynichromantique
> >> >
> >>
> >>
> >>
>
>
>

Try...

I2, copied down:

=IF(ISNUMBER(MATCH(A2,{"Coverage","Homeowners","Annuity","Blue
Cross","Group"},0)),"*","")

or

=IF(ISNUMBER(MATCH(A2,$J$2:$J$6,0)),"*","")

....where J2:J6 contains the list of 'specific words'.

Hope this helps!

In article <[email protected]>,
cynichromantique <[email protected]> wrote:

> I have a spreadsheet with over 30000 items listed in column A. I would like
> to be able to find specific column "A" cells with specific words and place an
> asterick in column "I" to designate that. So if column "A" contains any of
> the words I have listed, I want to place an asterick in the corresponding "I"
> cell.
>
> Thanks!

the way I normally do this is to simplify the formula by creating a list
elsewhere. In a different tab in the same worksheet list the words you
are looking for.

Then in the worksheet you desire the output use the vlookup function:

=vlookup(lookupcell,listofwords,1,false)

now this will give you an error code if the word is not there and give
the actual word when it is there. Therefore to further refine:

=if(iserror(vlookup(lookupcell,listofwords,1,false)),"","*")

That should do the trick.

Alternatively you can use the OR fuction:

=if(OR(a1="This",a1="That",a1="Anything"),"*","")

Obviously if the list is small then OR will do the trick otherwise I
reccomend vlookup using a list.


--
Gerry-W