Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 6th 07, 07:40 PM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2007
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  
Old 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
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  
Old 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
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  
Old 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
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 09:56 AM
Identifying Part Numbers Tiziano Excel Worksheet Functions 3 December 15th 06 08: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 10:35 PM
Modification of Part Numbers Tiziano Excel Discussion (Misc queries) 3 February 26th 05 06:32 PM


All times are GMT +1. The time now is 03:42 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017