Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
Posts: 12
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Part Numbers Matt Carter Excel Discussion (Misc queries) 2 February 19th 07 10:56 AM
Identifying Part Numbers Tiziano Excel Worksheet Functions 3 December 15th 06 09:05 AM
Replace Old Part Numbers with New Part Numbers in a Macro. Jeffery Keown Excel Discussion (Misc queries) 5 October 17th 06 03:45 PM
FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS ANDY CALLAGHAN Excel Discussion (Misc queries) 1 March 6th 05 11:35 PM
Modification of Part Numbers Tiziano Excel Discussion (Misc queries) 3 February 26th 05 07:32 PM


All times are GMT +1. The time now is 12:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"