Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    16

    Unanswered: Parsing string to get all numbers

    Hello all

    I need to find a quick way of retrieving all the numbers entered by a user.

    So if a user for example enters "3-8, 21, 27, 29-33" into a text field I need to extract from this

    3,4,5,6,7,8,21,27,29,30,31,32,33.

    If another user enters "4, 11, 22-25" I need to get "4,11,22,23,24,25" from this entry so that it can be saved into the database.

    Im not VBA expert - so how would this be done in the simplest way.

    My thoughts were to :-
    1) parse the string checking for the first comma
    2) then check for a dash
    3) extracting the numbers to the left and right of the dash (if there is one) and inserting into a loop as the min (number to the left of dash)and max (number to the right of dash) values to retrieve all the numbers inbetween to enter into the database.
    4) Repeat process until all numbers have been extracted.

    Sorry if this doesnt make sense - i am half asleep, and it has been some time since i have programmed and in VBA.

    Thanks in adv t.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    This should work but some verification for invalid characters in the original string list should be added:
    Code:
    Function ParseList(ByVal List As String) As String
    
        Dim var As Variant
        Dim i As Long
        Dim j As Long
        Dim x As Long
        Dim y As Long
        
        var = Split(List, ",")
        For i = 0 To UBound(var)
            var(i) = Trim(var(i))
            If InStr(var(i), "-") > 1 Then
                x = Left(var(i), InStr(var(i), "-") - 1)
                y = Mid(var(i), InStr(var(i), "-") + 1)
                var(i) = ""
                For j = x To y
                    If Len(var(i)) > 0 Then var(i) = var(i) & ","
                    var(i) = var(i) & CStr(j)
                Next j
            End If
            If Len(ParseList) > 0 Then ParseList = ParseList & ","
            ParseList = ParseList & var(i)
        Next i
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    16
    Hi sinndho

    Thank you for your post, am not at work atm but will give you feedback ASAP regarding the function that you have kindly helped me with!

    Thank you v much for you help

    t.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •