Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
cynichromantique
 
Posts: n/a
Default =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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

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" wrote in
message ...
Is it possible to add additional words to a statement of this nature?

Examples:

coverage
homeowners
annuity
blue cross
group

Thanks!

cynichromantique



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
cynichromantique
 
Posts: n/a
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

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" wrote in
message ...
Is it possible to add additional words to a statement of this nature?

Examples:

coverage
homeowners
annuity
blue cross
group

Thanks!

cynichromantique




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

Hi

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

=IF(AND(ISERROR(SEARCH("insurance",A125,1))),ISERR OR(SEARCH("coverage",a125,1)),ISERROR(SEARCH("home owners",a125,1)),ISERROR(SEARCH("annuity",a125,1)) ,ISERROR(SEARCH("bluecross",a125,1)),ISERROR(SEARC H("group",a125,1))),"","*")

This all goes into one cell, on one line.

Andy.




"cynichromantique" wrote in
message ...
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" wrote in
message ...
Is it possible to add additional words to a statement of this nature?

Examples:

coverage
homeowners
annuity
blue cross
group

Thanks!

cynichromantique






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
cynichromantique
 
Posts: n/a
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

Thanks!!!!!

"Andy" wrote:

Hi

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

=IF(AND(ISERROR(SEARCH("insurance",A125,1))),ISERR OR(SEARCH("coverage",a125,1)),ISERROR(SEARCH("home owners",a125,1)),ISERROR(SEARCH("annuity",a125,1)) ,ISERROR(SEARCH("bluecross",a125,1)),ISERROR(SEARC H("group",a125,1))),"","*")

This all goes into one cell, on one line.

Andy.




"cynichromantique" wrote in
message ...
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" wrote in
message ...
Is it possible to add additional words to a statement of this nature?

Examples:

coverage
homeowners
annuity
blue cross
group

Thanks!

cynichromantique









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

Try...

I2, copied down:

=IF(ISNUMBER(MATCH(A2,{"Coverage","Homeowners","An nuity","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 ,
cynichromantique 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!

  #7   Report Post  
Junior Member
 
Posts: 7
Default

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.
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

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("Eleva tion",C2,1)=1,"B_",IF(SEARCH("Elevation",C2,1)=1," C_","")))


Thanks in advance,

"Gerry-W" wrote:


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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

You could just look at the first x number of characters:

=if(left(c2,7)="roadway","A_",if(left(c2,9)="eleva tion","B_", ....



bridgemonkey wrote:

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("Eleva tion",C2,1)=1,"B_",IF(SEARCH("Elevation",C2,1)=1," C_","")))

Thanks in advance,

"Gerry-W" wrote:


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


--

Dave Peterson
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default =IF(ISERROR(SEARCH("insurance",A125,1)),"","*")

Sometimes its easy to get caught up in the moment and forget the basics ...
THANKS.

"Dave Peterson" wrote:

You could just look at the first x number of characters:

=if(left(c2,7)="roadway","A_",if(left(c2,9)="eleva tion","B_", ....



bridgemonkey wrote:

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("Eleva tion",C2,1)=1,"B_",IF(SEARCH("Elevation",C2,1)=1," C_","")))

Thanks in advance,

"Gerry-W" wrote:


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


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"