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

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

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

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

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

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