- **Excel Worksheet Functions**
(*https://www.excelbanter.com/excel-worksheet-functions/*)

- - **Help With Formula That Extract Part Numbers**
(*https://www.excelbanter.com/excel-worksheet-functions/454197-help-formula-extract-part-numbers.html*)

Help With Formula That Extract Part NumbersIn 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 |

Help With Formula That Extract Part NumbersHi,
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 |

Help With Formula That Extract Part NumbersWould 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 |

Help With Formula That Extract Part NumbersOn 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 |

Help With Formula That Extract Part NumbersOn 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 |

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 |

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

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

ExcelBanter.com