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 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
|