从字符之间提取数据

Question

有一个类似的问题得到了回答，但实际上它不太有效。我不知道是否有更好的方法来完成我的任务。 例如，我需要提取“（”和第三个“，”之间的数据 $$ Data_out(3,47,0,40,0,0,2,8.01) 并让结果为 3,47,0 我将在下面添加我的内容已经尝试过了

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

Answer 1

不同的方法：

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

Answer 2

阅读一些 VBA 教程以掌握基础知识。

这是使用工作表函数 Substitute 的替代方法，该函数具有实例参数，以便您可以挑选出第三个逗号。

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