Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 373

# Get values from a string

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?
0
isnoend2001
1 Solution

Commented:
Hope this funciton "GetNumAfter" will help:

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
0

Author Commented:
good job thanks
0

Commented:
Copy your source strings to a worksheet starting from A1.
Place this code in a normal code module and run the Sub Test
Option Explicit
Option Base 0

Private Sub Test()

Const Col As String = "A"   ' column where the source strings are

Dim Ws As Worksheet
Dim Temp As String          ' Source string
Dim S() As String           ' subdivided Source string
Dim R As Long               ' Row number
Dim Numbers() As Double     ' numbers extracted from the source string
Dim i As Long

Set Ws = ActiveSheet
With Ws
For R = 1 To LastRow(Col, Ws)
Temp = .Cells(R, Col).Value
Temp = Application.WorksheetFunction.Substitute(Temp, Chr(160), Chr(32))
S = Split(Temp)
Numbers = ExtractedNumbers(S)
MsgBox S(0) & " = " & Numbers(0) & vbCr & _
"High dollar amount = " & Numbers(1) & vbCr & _
"Low credit = " & Numbers(2) & vbCr & _
"Result = " & Numbers(3)
Next R
End With
End Sub

Private Function ExtractedNumbers(S() As String) As Double()

Dim Numbers(0 To 3) As Double   ' numbers extracted from the source string
Dim n As Long                   ' array index
Dim i As Long                   ' array index
Dim Temp As String

For n = 0 To UBound(S)
Temp = Trim(S(n))
If Left(Temp, 1) = "\$" Then
Temp = Trim(Mid(Temp, 2))
End If
If IsNumeric(Temp) Then
Numbers(i) = Val(Temp)
i = i + 1
End If
Next n
ExtractedNumbers = Numbers
End Function

Private Function LastRow(Optional ByVal Col As Variant, _
Optional Ws As Worksheet) As Long
' 0059 V 3.2 Apr 2, 2012

' Return the number of the last non-blank row in column Col.
' Specify the column as string or number
' If no column is specified,
' return the last row from column A.
' If no worksheet is specified
' return the result from the currently active sheet.

Dim R As Long

If Ws Is Nothing Then Set Ws = ActiveSheet
If VarType(Col) = vbError Then Col = 1
With Ws
R = .Cells(.Rows.Count, Col).End(xlUp).Row
With .Cells(R, Col)
' in a blank column the last used row is 0 (= none)
If R = 1 And .Value = vbNullString Then R = 0
' include all rows of a merged range
LastRow = R + .MergeArea.Rows.Count - 1
End With
End With
End Functio
The code extracts the numbers but doesn't total them up. I think doing the total will not be a problem for you. I also think that you will be able to read the code I am providing,. Should you need assistance, however, please do let me know.
0

Commented:
You can try this also:

Dim s As String
Dim n As Long
Dim nRuns As Double
Dim nLowC As Currency

For n = 0 To List1.ListCount - 1
s = List1.List(n)

nRuns = nRuns + Val(Split(s, "Runs:", 2)(1))
nLowC = nLowC + Val(Split(s, "low credit \$", 2)(1))
Next
Debug.Print nRuns, Format(nLowC, "\$###,###.##")
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.