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 in vba image.

Can you help me for the vba code



Column B= data



Column D = the result i wanna get



The workbook is attached



Kind regards,

The critéria of these nnumbers is , the always begin by DE or SK or NM or TV or BN or KS and following by 9 Digits

I hope that will help you

Kind regards

Hello gijsmo,

Thank you for your help , it's working

Kind regards,