SEARCH function - trying to exclude some terms and include other terms

Georgia1

New Member
Joined
Sep 11, 2011
Messages
7
Hi

I have several thousand lines of chemical data as shown in columns A-E below. I am trying to sort this into some chemical groupings using specific text (part of the entry) in Column D.
I am using the following formula in Column F to search the entries in Column D, and then list the name from Column A (in Column F) if the wanted search terms are found.

My difficulty is that I want to exclude certain search terms, but I cant work out how to add this into my formula:

=IF((SEARCH({"36","38","36/38"},D3)),A3,"-")

The search terms I want to include are: "36", "38", "36/38"
The search terms I want to exclude are: "37", "36/37", "37/38", "36/37/38"

I would be very grateful for any advice.

Georgia

[TABLE="width: 1311"]
<tbody>[TR]
[TD]Col A - Substance
[/TD]
[TD]Col B - CAS number
[/TD]
[TD]Col C - Symbol
[/TD]
[TD]Col D - Risk-phrases
[/TD]
[TD]Col E - Safety phrases
[/TD]
[TD]Col F - Group A items identified
[/TD]
[TD]LInes that should excluded
[/TD]
[/TR]
[TR]
[TD]ACEPHATE
[/TD]
[TD]30560-19-1
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2)-36
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETAL
[/TD]
[TD]105-57-7
[/TD]
[TD]F,Xi
[/TD]
[TD]11-36/38
[/TD]
[TD](2-)9-16-33
[/TD]
[TD]ACETAL
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETALDEHYDE
[/TD]
[TD]75-07-0
[/TD]
[TD]F+,Xn
[/TD]
[TD]12-36/37-40
[/TD]
[TD](2-)16-33-36/37
[/TD]
[TD]ACETALDEHYDE
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]ACETAMIDE
[/TD]
[TD]60-35-5
[/TD]
[TD]Xn
[/TD]
[TD]40
[/TD]
[TD](2-)36/37
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc.>90%
[/TD]
[TD]64-19-7
[/TD]
[TD]C
[/TD]
[TD]Oct-35
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc. 25%-90%
[/TD]
[TD][/TD]
[TD]C
[/TD]
[TD]34
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ANHYDRIDE
[/TD]
[TD]108-24-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE
[/TD]
[TD]67-64-1
[/TD]
[TD]F
[/TD]
[TD]11
[/TD]
[TD](2-)9-16-23-33
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE CYANOHYDRIN
[/TD]
[TD]75-86-5
[/TD]
[TD]T+,N
[/TD]
[TD]26/27/28-50
[/TD]
[TD](1/2-)7/9-27-45-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONITRILE
[/TD]
[TD]75-05-8
[/TD]
[TD]F,T
[/TD]
[TD]11-23/24/25
[/TD]
[TD](1/2-)16-27-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETOPHENONE
[/TD]
[TD]98-86-2
[/TD]
[TD]Xn
[/TD]
[TD]22-36
[/TD]
[TD](2-)26
[/TD]
[TD]ACETOPHENONE
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLACETONE
[/TD]
[TD]123-54-3
[/TD]
[TD]Xn
[/TD]
[TD]Oct-22
[/TD]
[TD](2-)21-23-24/25
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYL CHLORIDE
[/TD]
[TD]75-36-5
[/TD]
[TD]F,C
[/TD]
[TD]11-14-34
[/TD]
[TD](1/2-)9-16-26-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLENE
[/TD]
[TD]74-86-2
[/TD]
[TD]F+
[/TD]
[TD]5/06/2012
[/TD]
[TD](2-)9-16-33
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-ACETYL-6-METHYL-2H-PYRAN-2,4(3H)-DIONE
[/TD]
[TD]520-45-6
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACLONIFEN
[/TD]
[TD]74070-46-5
[/TD]
[TD]N
[/TD]
[TD]50/53
[/TD]
[TD]60-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE
[/TD]
[TD]302-27-2
[/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE, SALTS
[/TD]
[TD][/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45 Note A
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACROLEIN
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLALDEHYDE
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLAMIDE
[/TD]
[TD]79-06-1
[/TD]
[TD]T
[/TD]
[TD]45-46-24/25-48/23/24/25
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD][/TD]
[TD]Xi
[/TD]
[TD]36/37/38
[/TD]
[TD](2-)26-28
[/TD]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]specified elsewhere in this Annex, conc.³10%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLIC ACID
[/TD]
[TD]79-10-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-36-45 Note D
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLONITRILE
[/TD]
[TD]107-13-1
[/TD]
[TD]F,T
[/TD]
[TD]45-11-23/24/25-38
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADIPIC ACID
[/TD]
[TD]124-04-9
[/TD]
[TD]Xi
[/TD]
[TD]36
[/TD]
[TD="align: right"]-2
[/TD]
[TD]ADIPIC ACID
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ADZN
[/TD]
[TD]78-67-1
[/TD]
[TD]E,Xn
[/TD]
[TD]2-11-20/22
[/TD]
[TD](2-)39-41-47
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALACHLOR
[/TD]
[TD]15972-60-8
[/TD]
[TD]Xn
[/TD]
[TD]22-40-43
[/TD]
[TD](2-)36/37/39
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDICARB
[/TD]
[TD]116-06-3
[/TD]
[TD]T+
[/TD]
[TD]27/28
[/TD]
[TD](1/2-)22-36/37-45
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDRIN
[/TD]
[TD]309-00-2
[/TD]
[TD]T,N
[/TD]
[TD]24/25-40-48/24/25-50/53
[/TD]
[TD](1/2-)22-36/37-45-60-61
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLETHRIN
[/TD]
[TD]584-79-2
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2-)36
[/TD]
[TD="align: center"]#VALUE!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLIDOCHLOR
[/TD]
[TD]93-71-0
[/TD]
[TD]Xn
[/TD]
[TD]21/22-36/38
[/TD]
[TD](2-)26-28-36/37/39
[/TD]
[TD]ALLIDOCHLOR
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLYL ALCOHOL
[/TD]
[TD]107-18-6
[/TD]
[TD]T,N
[/TD]
[TD]10-23/24/25-36/37/38-50
[/TD]
[TD](1/2-)36/37/39-38-45-61
[/TD]
[TD]ALLYL ALCOHOL
[/TD]
[TD]Should exclude
[/TD]
[/TR]
</tbody>[/TABLE]
 
Try this

F3
=IF(AND(SUM(--ISNUMBER(SEARCH({"36","38","36/38"},D3))),SUM(--ISNUMBER(SEARCH({"37","36/37","37/38","36/37/38"},D3)))=0),A3,"-")
copy down

M.
 
Upvote 0
Try this

F3
=IF(AND(SUM(--ISNUMBER(SEARCH({"36","38","36/38"},D3))),SUM(--ISNUMBER(SEARCH({"37","36/37","37/38","36/37/38"},D3)))=0),A3,"-")
copy down

M.

Hi Marcelo

Thankyou so much for the better formula. It works better and does not give me #value for an answer! I have copied it down the lines from F3 and marked in which outcomes are correct for my purposes and which are not (Column G). As you can see it is including some entries that it should not be. Here is a summary of what must be included and excluded when searching in Column D:

Hazard Group A
Include:
36
38
36/38

Exclude:
36/37
36/37/38
37/38


Is there something I need to do to the search terms "36" and "38" to stop the formula including entries such as "36/37"? The data in column D is pretty messy.

Many thanks again for your help.

Georgia
[TABLE="width: 1420"]
<tbody>[TR]
[TD]Col A - Substance
[/TD]
[TD]Col B - CAS number
[/TD]
[TD]Col C - Symbol
[/TD]
[TD]Col D - Risk-phrases
[/TD]
[TD]Col E - Safety phrases
[/TD]
[TD]Col F - Group A items identified
[/TD]
[TD]Col G - Should include or exclude
[/TD]
[/TR]
[TR]
[TD]ACEPHATE
[/TD]
[TD]30560-19-1
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2)-36
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETAL
[/TD]
[TD]105-57-7
[/TD]
[TD]F,Xi
[/TD]
[TD]11-36/38
[/TD]
[TD](2-)9-16-33
[/TD]
[TD]ACETAL
[/TD]
[TD]Should include
[/TD]
[/TR]
[TR]
[TD]ACETALDEHYDE
[/TD]
[TD]75-07-0
[/TD]
[TD]F+,Xn
[/TD]
[TD]12-36/37-40
[/TD]
[TD](2-)16-33-36/37
[/TD]
[TD]ACETALDEHYDE
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]ACETAMIDE
[/TD]
[TD]60-35-5
[/TD]
[TD]Xn
[/TD]
[TD]40
[/TD]
[TD](2-)36/37
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc.>90%
[/TD]
[TD]64-19-7
[/TD]
[TD]C
[/TD]
[TD]Oct-35
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ACID, conc. 25%-90%
[/TD]
[TD][/TD]
[TD]C
[/TD]
[TD]34
[/TD]
[TD](1/2-)23-26-45 Note B
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETIC ANHYDRIDE
[/TD]
[TD]108-24-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE
[/TD]
[TD]67-64-1
[/TD]
[TD]F
[/TD]
[TD]11
[/TD]
[TD](2-)9-16-23-33
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONE CYANOHYDRIN
[/TD]
[TD]75-86-5
[/TD]
[TD]T+,N
[/TD]
[TD]26/27/28-50
[/TD]
[TD](1/2-)7/9-27-45-61
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETONITRILE
[/TD]
[TD]75-05-8
[/TD]
[TD]F,T
[/TD]
[TD]11-23/24/25
[/TD]
[TD](1/2-)16-27-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETOPHENONE
[/TD]
[TD]98-86-2
[/TD]
[TD]Xn
[/TD]
[TD]22-36
[/TD]
[TD](2-)26
[/TD]
[TD]ACETOPHENONE
[/TD]
[TD]Should include
[/TD]
[/TR]
[TR]
[TD]ACETYLACETONE
[/TD]
[TD]123-54-3
[/TD]
[TD]Xn
[/TD]
[TD]Oct-22
[/TD]
[TD](2-)21-23-24/25
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYL CHLORIDE
[/TD]
[TD]75-36-5
[/TD]
[TD]F,C
[/TD]
[TD]11-14-34
[/TD]
[TD](1/2-)9-16-26-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACETYLENE
[/TD]
[TD]74-86-2
[/TD]
[TD]F+
[/TD]
[TD]5/06/2012
[/TD]
[TD](2-)9-16-33
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-ACETYL-6-METHYL-2H-PYRAN-2,4(3H)-DIONE
[/TD]
[TD]520-45-6
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACLONIFEN
[/TD]
[TD]74070-46-5
[/TD]
[TD]N
[/TD]
[TD]50/53
[/TD]
[TD]60-61
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE
[/TD]
[TD]302-27-2
[/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACONITINE, SALTS
[/TD]
[TD][/TD]
[TD]T+
[/TD]
[TD]26/28
[/TD]
[TD](1/2-)24-45 Note A
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACROLEIN
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLALDEHYDE
[/TD]
[TD]107-02-8
[/TD]
[TD]F,T+
[/TD]
[TD]11-25-26-34
[/TD]
[TD](1/2-)3/9/14-26-36/37/39-38-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLAMIDE
[/TD]
[TD]79-06-1
[/TD]
[TD]T
[/TD]
[TD]45-46-24/25-48/23/24/25
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD][/TD]
[TD]Xi
[/TD]
[TD]36/37/38
[/TD]
[TD](2-)26-28
[/TD]
[TD]ACRYLATES, with the exception of those
[/TD]
[TD]Should exclude
[/TD]
[/TR]
[TR]
[TD]specified elsewhere in this Annex, conc.³10%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLIC ACID
[/TD]
[TD]79-10-7
[/TD]
[TD]C
[/TD]
[TD]Oct-34
[/TD]
[TD](1/2-)26-36-45 Note D
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACRYLONITRILE
[/TD]
[TD]107-13-1
[/TD]
[TD]F,T
[/TD]
[TD]45-11-23/24/25-38
[/TD]
[TD]53-45 Note D, E
[/TD]
[TD]ACRYLONITRILE
[/TD]
[TD]Should include
[/TD]
[/TR]
[TR]
[TD]ADIPIC ACID
[/TD]
[TD]124-04-9
[/TD]
[TD]Xi
[/TD]
[TD]36
[/TD]
[TD="align: right"]-2
[/TD]
[TD]ADIPIC ACID
[/TD]
[TD]Should include
[/TD]
[/TR]
[TR]
[TD]ADZN
[/TD]
[TD]78-67-1
[/TD]
[TD]E,Xn
[/TD]
[TD]2-11-20/22
[/TD]
[TD](2-)39-41-47
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALACHLOR
[/TD]
[TD]15972-60-8
[/TD]
[TD]Xn
[/TD]
[TD]22-40-43
[/TD]
[TD](2-)36/37/39
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDICARB
[/TD]
[TD]116-06-3
[/TD]
[TD]T+
[/TD]
[TD]27/28
[/TD]
[TD](1/2-)22-36/37-45
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALDRIN
[/TD]
[TD]309-00-2
[/TD]
[TD]T,N
[/TD]
[TD]24/25-40-48/24/25-50/53
[/TD]
[TD](1/2-)22-36/37-45-60-61
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLETHRIN
[/TD]
[TD]584-79-2
[/TD]
[TD]Xn
[/TD]
[TD]22
[/TD]
[TD](2-)36
[/TD]
[TD]-
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ALLIDOCHLOR
[/TD]
[TD]93-71-0
[/TD]
[TD]Xn
[/TD]
[TD]21/22-36/38
[/TD]
[TD](2-)26-28-36/37/39
[/TD]
[TD]ALLIDOCHLOR
[/TD]
[TD]Should include
[/TD]
[/TR]
[TR]
[TD]ALLYL ALCOHOL
[/TD]
[TD]107-18-6
[/TD]
[TD]T,N
[/TD]
[TD]10-23/24/25-36/37/38-50
[/TD]
[TD](1/2-)36/37/39-38-45-61
[/TD]
[TD]ALLYL ALCOHOL
[/TD]
[TD]Should exclude
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi Marcelo

I am sorry but I made a mistake in the formula when copying it down. It does work. It does exclude the entries it should.

Thankyou so much for your help.

Georgia
 
Upvote 0
Hi Marcelo

I am sorry but I made a mistake in the formula when copying it down. It does work. It does exclude the entries it should.

Thankyou so much for your help.

Georgia

You are welcome. Glad for helping :mad:

M.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top