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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Andy said:
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.
Click to expand...
 
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!
 
Similar situation sort of:
I have a column with photo captions. If the caption contains the word
"Roadway", I need a "A_" returned in adjacent column, If the caption contains
the word "Elevation", I need "B_" returned in adjacent column. Same with
"General" to "C_". By the way, the "key" word will always start with
character 1, hence the "=1". The problem I'm having is when "Roadway" does
not exist, the value returned is "#VALUE" which apparently cannot be
evaluated and doesn't seem to hit the second IF .... never seen ISERROR
before so maybe that will work,

This is what I have so far:
=IF(SEARCH("Roadway",C2,1)=1,"A_",IF(SEARCH("Elevation",C2,1)=1,"B_",IF(SEARCH("Elevation",C2,1)=1,"C_","")))


Thanks in advance,
 
You could just look at the first x number of characters:

=if(left(c2,7)="roadway","A_",if(left(c2,9)="elevation","B_", ....
 
Sometimes its easy to get caught up in the moment and forget the basics ...
THANKS.
 
Back
Top