Home 
Search 
Today's Posts 
#1




Help With Formula That Extract Part Numbers
In column B I have part numbers like this:
6520 0402 U77, i.e. 6520 0402(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 0604, i.e. U77K(space)(underscore)6530 0604 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 0604. To recap: * If the p/n in col. B is something like 6520 0402 U77, the formula would extract: 6520 0402 * If the p/n in col. B is something like U77K _6530 0604, the formula would extract 6530 0604 Could anybody please help me in modifying my formula? I've been trying and trying without success... Thanks.  tb 
#2




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 0402 U77, the formula would extract: 6520 0402 * If the p/n in col. B is something like U77K _6530 0604, the formula would extract 6530 0604 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




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.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#4




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 0402 U77, the formula would extract: 6520 0402 * If the p/n in col. B is something like U77K _6530 0604, the formula would extract 6530 0604 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




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




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.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Need Formula For Extracting Part Numbers  Excel Worksheet Functions  
Formula For Sorting Part Numbers  Excel Worksheet Functions  
Formula For Labeling Part Numbers  Excel Worksheet Functions  
Replace Old Part Numbers with New Part Numbers in a Macro.  Excel Discussion (Misc queries)  
FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS  Excel Discussion (Misc queries) 