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

[Arthur_75]

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

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,

[Paul_Hossler]

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


28997



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

[Arthur_75]

Thank you

It's working

Kind regards

[snb]

Or


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