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

G

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>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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
Marcelo Branco said:
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.
Click to expand...

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>
 
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
Georgia1 said:
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
Click to expand...

You are welcome. Glad for helping :mad:

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

MartinS
  • Question
Update FormatConditions Shifts Column/Row Reference
Replies
0
Views
39
MartinS
MartinS
K
  • Question
conditional formatting question
Replies
2
Views
156
kerm007
K
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
308
awoohaw
awoohaw
imback2nite
  • Solved
VBA help.
2
Replies
10
Views
361
Peter_SSs
Peter_SSs
Dazzawm
  • Solved
Macro To Find Duplicates And Put In Another Column
Replies
3
Views
96
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,805
Messages
6,121,665
Members
449,045
Latest member
Marcus05

Share this page

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