How to extract a specific data in a column and parse it in another column with VBA

Hello everyone ,

I have some trouble to solve this issue

So my goal is to extract a specific information in a column and parse it to another column like this

Attachment 28991



Can you help me for the vba code

Column B= data

Column D = the result i wanna get

The workbook is attached

Kind regards,

I think a User Defined Function (UDF) is the way to go

Someone probably has a long complicated worksheet formula that does the same, but I prefer a UDF

ASSUMING that the Part number or whatever you want to extract has the same structure


Attachment 28997

Code:

---

Option Explicit


Function ExtractPart(s As String) As String
    Dim v As Variant
    Dim i As Long
   
    ExtractPart = Empty
   
    v = Split(s, " ")
   
    For i = LBound(v) To UBound(v)
        v(i) = UCase(Trim(v(i)))
        If Len(v(i)) = 11 Then
            If v(i) Like "[A-Z][A-Z]#########" Then
                ExtractPart = v(i)
                Exit Function
            End If
        End If
    Next i
End Function

---

Thank you

It's working

Kind regards

Or

Code:

---

Function F_snb(c00)
    For Each F_snb In Split(c00)
      If Len(F_snb) = 11 And Len(CStr(Val(Mid(F_snb, 3)))) = 9 Then Exit For
    Next
End Function

---