VBA fetch data from string

Question

Source Data: Source Data Image Sheet

Data to be pop: Data to be populated Sheet

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

Think you need to provide a bigger sample of data. Are they always in the same format as your single example? — SJR
– SJR, Commented May 2, 2020 at 12:40
QHarr: Yes they always occur in the same format and they are always in UPPER, thanks — Neel
– Neel, Commented May 2, 2020 at 12:49
Does your actual data only have the 4 strings or can there be more? — SJR
– SJR, Commented May 2, 2020 at 13:42

SJR · Accepted Answer · 2020-05-02 14:09:37Z

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

answered May 2, 2020 at 14:09

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Neel Over a year ago

This helps to an extent. I have added 2 sheets one with the exact source data another sheet where data to be populated. Thank you so much for your effort. Please have a look at the sheets.

SJR Over a year ago

I think I would turn it into a function and add the string as a parameter. Also I think that should be a separate question. Have a go yourself at trying to adapt what I've done, if you get stuck start a new question.

Neel Over a year ago

Yes I tried but didn't work with this data please help me with the code.

Neel Over a year ago

"stackoverflow.com/questions/61571509/vba-to-populate-sheet" Please help

Collectives™ on Stack Overflow

VBA fetch data from string

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related