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

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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,414
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.
 

Georgia1

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

<tbody>
</tbody>
 

Georgia1

New Member
Joined
Sep 11, 2011
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,063
Members
414,498
Latest member
jordanmiller7890

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
Top