Formula For Labeling Part Numbers

[tb]

Column A of my spreadsheet has a bunch of part numbers. I would like
to create a formula in Column B such that my parts are either labeled
"American" or "Metric".

Parts that are to be labeled "American", have one (or more than one)
strings within their part number:

PT<space><space>
TF<space><space>
<space>NPTF
<space>NPS
<space>DOT<space>
<space>UNF
<space>NPT
<space>NFPA

If none of the above strings are within a part number, then the part
is to be labeled "Metric".

Can somebody please help me in creating the desired formula?

Thanks.

 

[T. Valko]

Create a table with your strings including the spaces.

See this screncap:

http://img228.imageshack.us/img228/1858/americanod9.jpg

Here's the formula entered in B2 then copied down:

=IF(A2="","",IF(ISTEXT(LOOKUP(2,1/(ISNUMBER(SEARCH(G$2:G$9,A2))),G$2:G$9)),"American","Metric"))

 

[Tiziano]

Create a table with your strings including the spaces.

See this screncap:

http://img228.imageshack.us/img228/1858/americanod9.jpg

Here's the formula entered in B2 then copied down:

=IF(A2="","",IF(ISTEXT(LOOKUP(2,1/(ISNUMBER(SEARCH(G$2:G$9,A2))),G$2:G$9)),"American","Metric"))

Thanks, Biff!

 

[T. Valko]

Thanks, Biff!

You're welcome!