Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

How do I group a list of numbers into a condensed form...

Hi,

I am trying to write a function to convert the following string...
   1, 2, 3, 4, 8, 9, 14, 16, 17 ,18
into...
   1-4, 8-9, 14, 16-18

Any help would be appreciated..

James
0
James Atkin
Asked:
James Atkin
  • 2
1 Solution
 
RogueSolutionsCommented:
Well, its not the neatest solution but I think it works OK :)

Public Sub test()

    Dim sInput      As String
    Dim sOutput     As String
    Dim sNums()     As String
    Dim iNum        As Integer
    Dim bInRange    As Boolean
   
   
    sInput = "1, 2, 3, 4, 8, 9, 14, 16, 17 ,18 "
   
    sNums = Split(Replace(sInput, " ", ""), ",")
    'sOutput = sNums(LBound(sNums))
   
    For iNum = LBound(sNums) To UBound(sNums) - 1
       
        If Val(sNums(iNum + 1)) = Val(sNums(iNum)) + 1 Then
           
            If Not bInRange Then
                If Right(sOutput, 2) = ", " Or sOutput = "" Then
                    sOutput = sOutput & sNums(iNum)
                Else
                    sOutput = sOutput & ", " & sNums(iNum)
                End If
            End If
           
            bInRange = True
       
        ElseIf bInRange Then
           
            sOutput = sOutput & " - " & sNums(iNum) & ", "
            bInRange = False
           
        Else
           
            If Right(sOutput, 2) = ", " Then
                sOutput = sOutput & sNums(iNum)
            Else
                sOutput = sOutput & ", " & sNums(iNum)
            End If
           
        End If
           
    Next
   
    If bInRange Then
        sOutput = sOutput & " - " & sNums(UBound(sNums))
    Else
        If Right(sOutput, 2) = ", " Then
            sOutput = sOutput & sNums(iNum)
        Else
            sOutput = sOutput & ", " & sNums(iNum)
        End If
    End If
   
    Debug.Print sOutput
   
End Sub
0
 
James AtkinSenior Principle Software EngineerAuthor Commented:
Looks good to me...

Doesn't need to be optimised, just work well!!!

Thanks for the help,

James
0
 
RogueSolutionsCommented:
The code above did a comma at the beginning if the first number wasn't a "range" so ....

Public Sub test()

    Dim sInput      As String
    Dim sOutput     As String
    Dim sNums()     As String
    Dim iNum        As Integer
    Dim bInRange    As Boolean
   
   
    sInput = "1, 3, 4, 8, 9, 14, 15, 16, 17 ,18 ,20"
   
    sNums = Split(Replace(sInput, " ", ""), ",")
    'sOutput = sNums(LBound(sNums))
   
    For iNum = LBound(sNums) To UBound(sNums) - 1
       
        If Val(sNums(iNum + 1)) = Val(sNums(iNum)) + 1 Then
           
            If Not bInRange Then
                If Right(sOutput, 2) = ", " Or sOutput = "" Then
                    sOutput = sOutput & sNums(iNum)
                Else
                    sOutput = sOutput & ", " & sNums(iNum)
                End If
            End If
           
            bInRange = True
       
        ElseIf bInRange Then
           
            sOutput = sOutput & " - " & sNums(iNum) & ", "
            bInRange = False
           
        Else
           
            If Right(sOutput, 2) = ", " Or sOutput = "" Then
                sOutput = sOutput & sNums(iNum)
            Else
                sOutput = sOutput & ", " & sNums(iNum)
            End If
           
        End If
           
    Next
   
    If bInRange Then
        sOutput = sOutput & " - " & sNums(UBound(sNums))
    Else
        If Right(sOutput, 2) = ", " Then
            sOutput = sOutput & sNums(iNum)
        Else
            sOutput = sOutput & ", " & sNums(iNum)
        End If
    End If
   
    Debug.Print sOutput
   
End Sub

This one fixes that.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now