Extract data from between characters-CodePudding

there was a similar question answered but in practice it doesn't quite work. I don't know if there is a better way to accomplish my task. I need to extract the data between "(" and the third "," for example $$ Data_out(3,47,0,40,0,0,2,8.01) and having the result be 3,47,0 I will add below what I've tried

Dim str As String
Dim openPos As Integer
Dim closePos As Integer
Dim midBit As String
ActiveSheet.Range("A2").Activate
Do While Range("A:A").Cells(i, 1).Value <> ""

 On Error Resume Next
openPos = InStr(str, "(")
 On Error Resume Next
closePos = InStr(str, ",0,")
 On Error Resume Next
midBit = Mid(str, openPos   1, closePos - openPos - 1)
extract_value = midBit

ActiveCell.Value = midBit

i = i   1
Loop

CodePudding user response：

Read some VBA tutorials to master the basics.

Here's an alternative which use the worksheet function Substitute which has an instance parameter so you can pick out the third comma.

Sub x()

Dim str As String
Dim openPos As Long, closePos As Long
Dim midBit As String
Dim r As Long

For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
    openPos = InStr(Cells(r, 1), "(")
    str = WorksheetFunction.Substitute(Cells(r, 1), ",", "@", 3) '@ or any character not likely to appear in your data
    closePos = InStr(str, "@")
    If openPos > 0 And Len(str) > 0 Then
        midBit = Mid(str, openPos   1, closePos - openPos - 1)
        Cells(r, 1).Value = midBit
    End If
Next r

End Sub

CodePudding user response：

Different approach:

Sub Tester()
    Dim txt, txt2, arr
    txt = "$$ Data_out(3,47,0,40,0,0,2,8.01)" 'input...
    txt2 = Split(txt, "(")(1)                 'everything after first "("
    arr = Split(txt2, ",")                    'split on comma
    ReDim Preserve arr(0 To 2)                'first 3 elements only
    Debug.Print Join(arr, ",")                'join to string
End Sub