building a text from a range of numbers...

[webwriter]

Hi..
I am trying to build a 22 character text field that will list a range of pallet numbers for a program i am writing.. ex. I have a list of pallet numbers.. "1 2 5 6 7 8 12 15 16 17 18". I need the text string to reflect the pallet numbers in a format like this or something similar.. "1,2,5-8,12,15-17". Does anyone know how to generate code that would build my text without listing each number sequentially?

Thanks for the help...

webwriter

 

[dilettante]

Ok, there is a lot I don't like about this but it does work:

Function MrLister(ByVal strSequence)
  'Take a comma-delimited sequence of
  'integers and return a "list" where
  'runs of at least "clngGranularity"
  'have been hyphenated.
  '
  'Gran=1: "1,2,7,8,9" becomes "1-2,7-9"
  'Gran=2: "1,2,7,8,9" becomes "1,2,7-9"
  'Gran=3: "1,2,7,8,9" becomes "1,2,7,8,9"
  '
  'Choose a value for "cstrStop" that fits
  'your application.  Any value that can
  'never occur in "strSequence" may be
  'used.

  Const clngGranularity = 2
  Const cstrStop = "-65535"
  Dim i, j
  Dim aryNums
  Dim lngRun
  Dim lngDiff
  Dim strList

  strSequence = cstrStop & "," _
              & strSequence & "," _
              & cstrStop
  aryNums = Split(strSequence, ",")
  lngRun = 0
  For i = 1 to UBound(aryNums)
    aryNums(i) = Trim(aryNums(i))
    lngDiff = CLng(aryNums(i)) _
            - CLng(aryNums(i - 1))
    If lngDiff = 1 Then
      lngRun = lngRun + 1
    Else
      If lngRun >= clngGranularity Then
        strList = strList _
                & "-" & aryNums(i - 1)
        lngRun = 0
      End If
      For j = lngRun to 0 step -1
        If aryNums(i - j) <> cstrStop Then
          strList = strList _
                  & &quot;,&quot; & aryNums(i - j)
        End If
      Next
      lngRun = 0
    End If
  Next
  'Trim leading comma!
  MrLister = Mid(strList, 2)
End Function

'Test program ----------------------
Dim strIn
Dim bEnd
Dim strOut

Do 'Loop to test the algorithm.
  strIn = _
    InputBox(&quot;List of numbers or END:&quot;)
  If UCase(Trim(strIn)) <> &quot;END&quot; Then
    strOut = MrLister(strIn)
    MsgBox &quot; In: &quot; & strIn & vbCrLf _
         & &quot;Out:&quot; & strOut
  Else
    bEnd = True
  End If
Loop Until bEnd
[/color]

Sorry about all the line-folding here. I was worried about the code wrpping in the forum here and having it be confusing.

Note the constant

clngGranularity

. If you can live with "Gran=1" where even "1,2,5" becomes "1-2,5" MrLister() can be greatly simplified.

I'm also unhappy about the "stopper" values and the bit where I trim off the leading comma.

Anybody got a better approach?