Remember Me?

#1
November 6th 07, 07:40 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2007 Posts: 12
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".

Thanks.
--
Tiziano

#2
November 7th 07, 04:07 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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".

Thanks.
--
Tiziano

#3
November 7th 07, 05:56 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 25
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
#4
November 7th 07, 06:27 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Matt Carter Excel Discussion (Misc queries) 2 February 19th 07 09:56 AM Tiziano Excel Worksheet Functions 3 December 15th 06 08:05 AM Jeffery Keown Excel Discussion (Misc queries) 5 October 17th 06 03:45 PM ANDY CALLAGHAN Excel Discussion (Misc queries) 1 March 6th 05 10:35 PM Tiziano Excel Discussion (Misc queries) 3 February 26th 05 06:32 PM

All times are GMT +1. The time now is 05:31 PM.