Solved

Get values from a string

Posted on 2013-01-31
4
357 Views
Last Modified: 2013-01-31
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
Comment
Question by:isnoend2001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 7

Accepted Solution

by:
Rahul_Gade earned 500 total points
ID: 38842525
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 Closing Comment

by:isnoend2001
ID: 38842560
good job thanks
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38842572
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

Open in new window

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
 
LVL 15

Expert Comment

by:eemit
ID: 38842672
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, "$###,###.##")

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question