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

Question

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

enter image description here

Can you help me for the vba code

Column B= data

Column D = the result i wanna get

The workbook is attached

Kind regards,

score 0 · Accepted Answer · answered Sep 21 '21 at 09:50

Use a Regular Expression.This finds only the first instance if there are multiples.

Option Explicit

Sub RegexMatch()

    Dim wb As Workbook, ws As Worksheet, cell As Range
    Dim lastRow As Long, r As Long, s As String

   ' build regex pattern
    Dim regex As Object, m As Object
    Set regex = CreateObject("vbscript.regexp")
    With regex
       .Global = False
       .MultiLine = False
       .IgnoreCase = True
       .Pattern = "([A-Z]{2}\d{9})" ' pattern AZ123456789
    End With

    ' data
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")
    lastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row

    For Each cell In ws.Range("B1").Resize(lastRow)
        s = cell.Value
        If regex.test(s) Then
            Set m = regex.Execute(s) '
            cell.Offset(0, 2) = m(0) ' matched term  in col D
        End If
    Next

    MsgBox lastRow & " rows parsed", vbInformation
End Sub

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

1 Answers1