Formula For Labeling Part Numbers
 

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
<spaceNPTF
<spaceNPS
<spaceDOT<space
<spaceUNF
<spaceNPT
<spaceNFPA

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.
--
Tiziano

Formula For Labeling Part Numbers
 

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"))

--
Biff
Microsoft Excel MVP


"tb" wrote in message
ups.com...
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
<spaceNPTF
<spaceNPS
<spaceDOT<space
<spaceUNF
<spaceNPT
<spaceNFPA

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.
--
Tiziano

Formula For Labeling Part Numbers
 

on 11/6/2007 10:07 PM T. Valko wrote the following:
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!
--
Tiziano

Formula For Labeling Part Numbers
 

"Tiziano" wrote in message
...
on 11/6/2007 10:07 PM T. Valko wrote the following:
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!
--
Tiziano

You're welcome!

--
Biff
Microsoft Excel MVP