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
<tbody>
</tbody>
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>