View Single Post
  #1   Report Post  
Old December 11th 18, 12:29 AM posted to microsoft.public.excel.worksheet.functions
tb tb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 78
Default Help With Formula That Extract Part Numbers

In column B I have part numbers like this:
6520 04-02 U77, i.e. 6520 04-02(space)(space)U77

I have created a formula in col. C that would extract that portion of
the part number _before_ the (space)(space). For instance, in cell C1
I have:
=if(iserror(search(" ",b1,1)),left(b1,40),left(b1,search(" ",b1,1)-1))

Basically, the formula looks for the content of cell B1 and will
extract everything that comes before the (space)(space). If the part
number in B1 does not have (space)(space), it will extract everything
starting from the left, up to 40 characters.

In addition to the p/n format above, we have now introduced some p/n
formatted like this:
U77K _6530 06-04, i.e. U77K(space)(underscore)6530 06-04

I need to modify my formula such that it will also extract everything
_after_ the (space)(underscore). So in the example above, the formula
would extract 6530 06-04.

To recap:
* If the p/n in col. B is something like 6520 04-02 U77, the formula
would extract: 6520 04-02
* If the p/n in col. B is something like U77K _6530 06-04, the formula
would extract 6530 06-04

Could anybody please help me in modifying my formula? I've been trying
and trying without success...

Thanks.
--
tb