Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #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

  #2   Report Post  
Old December 11th 18, 08:10 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,632
Default Help With Formula That Extract Part Numbers

Hi,

Am Mon, 10 Dec 2018 23:29:40 +0000 (UTC) schrieb tb:

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


try:
=IF(LEN(SUBSTITUTE(B1," ",))=LEN(B1)-2,LEFT(B1,FIND(" ",B1)-1),IF(LEN(SUBSTITUTE(B1," _",))=LEN(B1)-2,MID(B1,FIND(" _",B1)+2,20),B1))

Or do it with an UDF:

Function mySplit(myRng As Range) As String
If UBound(Split(myRng, " ")) = 1 Then
mySplit = Split(myRng, " ")(0)
ElseIf UBound(Split(myRng, " _")) = 1 Then
mySplit = Split(myRng, " _")(1)
Else
mySplit = myRng
End If
End Function

and call the function in the sheet with e.g.:
=mySplit(B1)


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old December 11th 18, 08:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 976
Default Help With Formula That Extract Part Numbers

Would you consider perhaps modifying your part numbering to a structured system
methodology? If so, have a look at PartNumbering.xls here...

https://app.box.com/s/vm9et35pdbaywfxi0lzd5r2qglkkf503

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #4   Report Post  
Old December 11th 18, 09:24 PM 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

On 12/11/2018 at 1:10:51 AM Claus Busch wrote:

Hi,

Am Mon, 10 Dec 2018 23:29:40 +0000 (UTC) schrieb tb:

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


try:
=IF(LEN(SUBSTITUTE(B1," ",))=LEN(B1)-2,LEFT(B1,FIND("
",B1)-1),IF(LEN(SUBSTITUTE(B1," _",))=LEN(B1)-2,MID(B1,FIND("
_",B1)+2,20),B1))

Or do it with an UDF:

Function mySplit(myRng As Range) As String
If UBound(Split(myRng, " ")) = 1 Then
mySplit = Split(myRng, " ")(0)
ElseIf UBound(Split(myRng, " _")) = 1 Then
mySplit = Split(myRng, " _")(1)
Else
mySplit = myRng
End If
End Function

and call the function in the sheet with e.g.:
=mySplit(B1)


Regards
Claus B.


Your formula works! Thanks Claus.

--
tb
  #5   Report Post  
Old December 12th 18, 05:40 PM 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

On 12/11/2018 at 1:16:17 PM GS wrote:

Would you consider perhaps modifying your part numbering to a
structured system methodology? If so, have a look at
PartNumbering.xls here...

https://app.box.com/s/vm9et35pdbaywfxi0lzd5r2qglkkf503


I'm afraid it's over my head, Garry...

Hope you're doing better healthwise. Merry Christmas!

--
tb


  #6   Report Post  
Old December 12th 18, 05:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 976
Default Help With Formula That Extract Part Numbers

On 12/11/2018 at 1:16:17 PM GS wrote:

Would you consider perhaps modifying your part numbering to a
structured system methodology? If so, have a look at
PartNumbering.xls here...

https://app.box.com/s/vm9et35pdbaywfxi0lzd5r2qglkkf503


I'm afraid it's over my head, Garry...

Hope you're doing better healthwise. Merry Christmas!


Thanks for the feedback. Yes, it takes a bit of study to absorb but I tried to
make it as easy as possible to understand without breaking it down with too
much reading. Perhaps over time it'll be of use!

All the best to you and yours...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


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
Need Formula For Extracting Part Numbers tb Excel Worksheet Functions 3 May 31st 16 10:57 PM
Formula For Sorting Part Numbers tb Excel Worksheet Functions 1 December 23rd 09 07:37 PM
Formula For Labeling Part Numbers tb Excel Worksheet Functions 3 November 7th 07 07:27 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


All times are GMT +1. The time now is 02:48 PM.

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