CODEWITHSUNDEEP
excelvbaextract
Jeff Herrera
Jeff Herrera

Reputation: 1

Extract data from between characters

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

Upvotes: 0

Views: 71

Answers (2)

Tim Williams
Tim Williams

Reputation: 166196

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

Upvotes: 2

SJR
SJR

Reputation: 23081

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

Upvotes: 1

Related Questions