在两个字符串之间查找值

Question

我有一根看起来像这样的绳子：

FirstValue: 1234磅-- SecondValue: 555盎司-- ThirdValue: 123 gal -~-

如何从字符串中提取每个值。我只需要"FirstValue:“和”-“或"SecondValue:”和--或"ThirdValue:“和”-~-“之间的值。

我有一个函数，它将找到第一个值，但当字符串中有多个"-~~“时，它似乎无法工作。

Function GetValue(infor As String, findVal As String, endval As String)
    GetValue = Mid(infor, Application.WorksheetFunction.Search(findVal, infor) + Len(findVal), Application.WorksheetFunction.Search(endval, infor) - Application.WorksheetFunction.Search(findVal, infor) - Len(findVal))
End Function

Sub test
    Debug.print GetValue(Range("A1").value, "FirstValue:", "-~~")
End Sub

Answer 1

请尝试下一个代码：

Sub ExtractNumbersFromString()
   Dim x As String, arr, El, arrFin, i As Long
   x = "FirstValue: 1234 lb -~~- SecondValue: 555 oz -~~- ThirdValue: 123 gal -~~-"

   arr = Split(x, ": ")
   ReDim arrFin(UBound(arr) - 1)

   For i = 1 To UBound(arr) 'skip the first element...
        arrFin(i - 1) = Split(arr(i), " ")(0)
   Next i
   Debug.Print Join(arrFin, "|")
End Sub

编辑的

下一个函数不使用任何分隔符。它使用'VBScript正则表达式‘，必须添加一个特定的引用：

Private Function extrNumb(strVal As String) As Variant
    'It needs adding a reference to Microsoft VBScript Regular Expressions x.x (usually 5.5)
    Dim allMatches As Object, match As Object, arr, i As Long
    With New RegExp
        .Pattern = "(\d{1,3},?)+(\.\d{2})?"
        .Global = True
        If .test(strVal) Then
            Set allMatches = .Execute(strVal)
        End If

        ReDim arr(allMatches.count - 1)
        For Each match In allMatches
          arr(i) = match.Value: i = i + 1
        Next
    End With
    extrNumb = arr
End Function

它可以通过这样的方式进行测试：

Sub testExtractNumbers() 'no any separator kept...
    Dim x As String, arr
    x = "FirstValue:1234 lb -~~- SecondValue 555 oz -~~- ThirdValue123 gal -~~-"
    arr = extrNumb(x)
    Debug.Print Join(arr, "|")
End Sub

为了自动添加所需的引用，请在使用上述代码之前运行下一段代码：

Sub addRegExpReference()
   'Add a reference to 'Microsoft VBScript Regular Expressions 5.5':
   On Error Resume Next
    ThisWorkbook.VBProject.References.AddFromGuid _
        GUID:="{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", _
        Major:=5, Minor:=5
    If err.Number = 32813 Then
        MsgBox "The reference already exists..."
    Else
        MsgBox "The reference added successfully..."
    End If
End Sub

Answer 2

使用VBA拆分命令。你得分开两次。使用"-“作为splut字符的拆分1将为您提供表单的字符串数组。对数组中的每个字符串执行的第二个拆分将在“：”字符上进行，以在数组的第二个位置(1位置)给出您的值。