Extract data from between characters

Question

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

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function — braX, Feb 22 '22 at 18:00
No that is not a definition, that is a declaration. You need to read braX's link. Neither do you define `i`. Those On Error lines are just masking all the problems. — SJR, Feb 22 '22 at 18:09
so i declared 'i' as long, how should i define 'str'? as you can tell im really not well versed in vba — Jeff Herrera, Feb 22 '22 at 18:18

score 2 · Answer 1 · answered Feb 22 '22 at 19:12

2

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

answered Feb 22 '22 at 19:12

Tim Williams

154,628
8
97
125

1

Just for fun: instead of `ReDim Preserve` you might indicate an index array of the wanted 1-based output order via `arr = Application.Index(arr, 0, Array(1, 2, 3))`. *Side note: this order can be changed.* C.f.: [Some pecularities of `Application.Index()`](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call) @TimWilliams – T.M. Feb 23 '22 at 12:56

score 1 · Answer 2 · answered Feb 22 '22 at 18:30

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

Maybe `str` shouldn't be used as a variable name either, since that shadows the [`Str`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/str-function) function? — BigBen, Feb 22 '22 at 18:38
that worked! thank you, i'll be sure to pay attention to the changes and learn a thing or two. thank you again! — Jeff Herrera, Feb 22 '22 at 18:43

Extract data from between characters

2 Answers2