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