View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
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