CODEWITHSUNDEEP
excelvba
pokemon_Man
pokemon_Man

Reputation: 902

Find value between two strings

I have a string that looks like this:

FirstValue: 1234 lb -~~- SecondValue: 555 oz -~~- ThirdValue: 123 gal -~~-

How can i extract each value from the string. I need the value only between the "FirstValue:" and "-~~-" or "SecondValue:" and "-~~-" or "ThirdValue:" and "-~~-"

I have an function that will find the first value, but it doesn't seem to work when it has multiple "-~~" in the string

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

Upvotes: 0

Views: 62

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next code:

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

Edited:

The next function does not use any separator. It uses 'VBScript Regular Expressions' and a specific reference must be added:

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

It can be tested in this way:

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

In order to automatically add the required reference, please run the next code before using the above ones:

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

Upvotes: 2

freeflow
freeflow

Reputation: 4355

Use the VBA Split command. You'll need to split twice. Split 1 using "-" as the splut character will give you an array of string of the form . The seconf split, done on each string in the array would be done on the ':' character to give your value in the second (1 position) of the array.

Upvotes: 0

Related Questions