0

I need a pattern to extract data from a given string. I tried many patterns but I could get it. I request you to please help me to solve this issue.

Input:

B01ACE595E_B011GGG3DE=[color_name], B01ACE595E_B07P9J73RB=[color_name], B01ACE595E_B082LT8LX8=[color_name], B01ACE595E_B07S3L4CXS=[size_name, color_name], B01ACE595E_B082L5R7RX=[size_name, color_name], B01ACE595E_B00TNJZQ2G=[color_name], B01ACE595E_B07B8DBXXK=[color_name], B01ACE595E_B01N7IODVS=[color_name], B01ACE595E_B0741H9VY9=[color_name], B01ACE595E_B07QXMYNP4=[color_name], B01ACE595E_B00X81JJP0=[color_name]

Output:

B01ACE595E_B011GGG3DE=[color_name]

B01ACE595E_B07P9J73RB=[color_name]

B01ACE595E_B082LT8LX8=[color_name]

Note: Every output should come in a new Cell one below the other.

Sub Regex_CPD()
Dim Regex As New RegExp
Dim inputstring As String
Dim matchresult As MatchCollection
Dim i As Long, lrow_1 As Long, lrow_2 As Long
Dim result As String
Dim item As Variant

lrow_1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lrow_2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1
With Regex
.Pattern = "[[A-Z]|[0-9]]+*e]"
'.Pattern = "\d+"
.Global = True
End With

Range("C1").Value = "Regex Value"
Range("D1").Value = "New_Parent"

For i = 2 To lrow_1
    inputstring = Range("A" & i).Value
    Set matchresult = Regex.Execute(inputstring)
    
    For Each item In matchresult
    
        result = item
        On Error Resume Next
        Range("C" & lrow_2).Value = result
        On Error Resume Next
        lrow_2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row + 1
    
    Next item

Next i
  • 3
    Why do you need to use Regex? a simple SPLIT(`string`, ", ") would give you an array you can then transpose and paste. – Tragamor Nov 10 '22 at 03:22

0 Answers0