[RESOLVED] Get values from a string

[isnoend07]

i am trying to get values from long strings of different lengths in a listbox;

Win Runs: 7329 high dollar amount 41.25 low credit $12.47 Result $41.48
Loss Runs: 6856 high dollar amount 25.80 low credit $40.52 Result $40.52
Win Runs: 136 high dollar amount 40.75 low credit $13.47 Result $40.52
Win Runs: 1354 high dollar amount 40.25 low credit $15.47 Result $40.00
Loss runs 13111 high dollar amount 35.80 low credit $41.48 Result $41.48

I am trying to addup all the runs eg:7329+6856+136+1354+13111 = 28,786
And
Low Credit $12.47+$40.52+$13.47+$15.47+$41.48 = 107.94
How can this be done?

[flyguille]

parsing the input?

what is the source of text lines? keyboard or a text file?.

[isnoend07]

Values are loaded from a file as strings

[ColinE66]

There are several ways of doing this, one of which is to use the Replace function to substitute all know text values with a delimiter and then use the Split function on the result

i.e.
s="Win Runs: 7329 high dollar amount 41.25 low credit $12.47 Result $41.48"
s=Replace(s,"Win Runs: ",vbnullstring)
s=Replace(s,"high dollar amount ","|")
s=Replace(s,"low credit $","|")
etc.

and you'll then be left with a string that looks like "7329|41.25|12.47|41.48"

The Split function will, of course, create an array of the four values. I'm sure you get the idea...

Define your Replace strings as constants for better code readability and ease-of-maintenance...

[Max187Boucher]

start with this

Private Sub Command1_Click()
Dim sText As String
Dim TextLine() As String
Dim NeededNumbers() As String
Dim AllRuns  As Currency
Dim AllLowCredit As Currency
Dim i As Integer, j As Integer

sText = "Win Runs: 7329 high dollar amount 41.25 low credit $12.47 Result $41.48" & vbNewLine & _
 "Loss Runs: 6856 high dollar amount 25.80 low credit $40.52 Result $40.52" & vbNewLine & _
 "Win Runs: 136 high dollar amount 40.75 low credit $13.47 Result $40.52" & vbNewLine & _
 "Win Runs: 1354 high dollar amount 40.25 low credit $15.47 Result $40.00" & vbNewLine & _
 "Loss runs 13111 high dollar amount 35.80 low credit $41.48 Result $41.48"
 
TextLine = Split(sText, vbNewLine)

For i = 0 To UBound(TextLine)
  NeededNumbers = Split(TextLine(i), Space(1))
    For j = 0 To UBound(NeededNumbers)
      If j = 2 Then AllRuns = AllRuns + NeededNumbers(j)
      If j = 6 Then AllLowCredit = AllLowCredit + NeededNumbers(j)
    Next j
Next i

Debug.Print AllRuns
Debug.Print AllLowCredit
End Sub

[Max187Boucher]

for all of them do this

Private Sub Command1_Click()
Dim sText As String
Dim TextLine() As String
Dim NeededNumbers() As String
Dim AllRuns  As Currency
Dim AllLowCredit As Currency
Dim AllHighDollar As Currency
Dim AllResult As Currency
Dim i As Integer, j As Integer

sText = "Win Runs: 7329 high dollar amount 41.25 low credit $12.47 Result $41.48" & vbNewLine & _
 "Loss Runs: 6856 high dollar amount 25.80 low credit $40.52 Result $40.52" & vbNewLine & _
 "Win Runs: 136 high dollar amount 40.75 low credit $13.47 Result $40.52" & vbNewLine & _
 "Win Runs: 1354 high dollar amount 40.25 low credit $15.47 Result $40.00" & vbNewLine & _
 "Loss runs 13111 high dollar amount 35.80 low credit $41.48 Result $41.48"
 
TextLine = Split(sText, vbNewLine)

For i = 0 To UBound(TextLine)
  NeededNumbers = Split(TextLine(i), Space(1))
    For j = 0 To UBound(NeededNumbers)
      If j = 2 Then AllRuns = AllRuns + NeededNumbers(j)
      If j = 6 Then AllHighDollar = AllHighDollar + NeededNumbers(j)
      If j = 9 Then AllLowCredit = AllLowCredit + NeededNumbers(j)
      If j = 11 Then AllResult = AllResult + NeededNumbers(j)
    Next j
    
Next i

Debug.Print AllRuns
Debug.Print AllHighDollar
Debug.Print AllLowCredit
Debug.Print AllResult
End Sub

tested but not tested to make sure every calculation is right (should be)

[isnoend07]

thanks that works

[ColinE66]

For i = 0 To UBound(TextLine)
  NeededNumbers = Split(TextLine(i), Space(1))
    For j = 0 To UBound(NeededNumbers)
      If j = 2 Then AllRuns = AllRuns + NeededNumbers(j)
      If j = 6 Then AllHighDollar = AllHighDollar + NeededNumbers(j)
      If j = 9 Then AllLowCredit = AllLowCredit + NeededNumbers(j)
      If j = 11 Then AllResult = AllResult + NeededNumbers(j)
    Next j
    
Next i

No need for a second loop if you do it that way. Just refer to the elements of 'NeededNumbers' like this:

For i = 0 To UBound(TextLine)
  NeededNumbers = Split(TextLine(i), Space(1))
      AllRuns = AllRuns + NeededNumbers(2)
      AllHighDollar = AllHighDollar + NeededNumbers(6)
      AllLowCredit = AllLowCredit + NeededNumbers(9)
      AllResult = AllResult + NeededNumbers(11)
Next i

[isnoend07]

Thanks for adding that I have decided to go with this:
Sub main()
Dim str As String
Dim run As String
str = "Win Runs: 7329 high dollar amount 41.25 low credit $12.47 Result $41.48"
runs = GetNumAfter(str, "Runs: ")
amount = GetNumAfter(str, "amount ")
credit = GetNumAfter(str, "credit $")
result = GetNumAfter(str, "Result $")
End Sub

Public Function GetNumAfter(MainString As String, SearchString As String)
Dim ResultValue As String
Dim pos1 As Integer
Dim pos2 As Integer
pos1 = InStr(MainString, SearchString) + Len(SearchString)
pos2 = InStr(pos1, MainString, " ")
If (pos2 = 0) Then pos2 = Len(MainString) + 1
ResultValue = Mid(MainString, pos1, pos2 - pos1)
GetNumAfter = ResultValue
End Function

[ColinE66]

There ya go. You knew how to do it all along ;-)