VBA fetch data from string

Question

Source Data:

Data to be pop:

I have a String like:

AV0(25CS,10P,5X)

AV0(10P,5X)

AV0(10P)

AV0(5X)

I have a table with column AV , CS, P , X I want to populate 0 in the cell below AV , 25 in the cell below CS , 10 in the cell below, 5 in the cell below X.

Please help.

I tried for CS:

str = Cells(1, 1).Value 'String Value
openPos = InStr(str, "(")
closePos = InStr(str, ")")
Cells(2, 2) = Mid(str, openPos + 1, closePos - openPos - 1) 'String value to cells

Answer 1

There is a faff using regular expressions as VBA does not support positive lookbehinds.

I'm not sure how you want to extract the results, I've just done it to the sheet below.

Sub Regex2()

Dim oMatches As Object, i As Long, r As Range

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "^AV(\d*)\((\d*CS)*,*(\d*P)*,*(\d*X)*\)$"
    For Each r In Range("A1:A4")
        Set oMatches = .Execute(r)
        For i = 0 To 3
            If oMatches(0).submatches(i) <> "" Then
                If i = 0 Then r.Offset(, 1) = oMatches(0).submatches(0) 'AV
                If i = 1 Then r.Offset(, 2) = Replace(oMatches(0).submatches(1), "CS", "") 'CS
                If i = 2 Then r.Offset(, 3) = Replace(oMatches(0).submatches(2), "P", "") 'P
                If i = 3 Then r.Offset(, 4) = Replace(oMatches(0).submatches(3), "X", "") 'X
            End If
        Next i
    Next r
End With

End Sub