VBA to Convert Spans of Numbers

[lneidorf]

Hi there.

I have a list of page spans that i need to convert into strings of the individual page numbers.

For example, for the cell value "1-4, 8-10", I need a script that will convert that to "1,2,3,4,8,9,10". My goal is to create a workbook that will loop through column A and enter the corresponding strings in column B.

I've scoured these boards as well as the Internets to no avail. Any suggestions would be most appreciated.

Thanks!

 

[Andrew Poulsom]

Here's my attempt:

Sub Test()
    MsgBox Expand("1-4, 6, 8-10")
End Sub

Function Expand(List As String) As String
    Dim ArrAll As Variant
    Dim i As Long
    Dim ArrRange As Variant
    Dim ii As Long
    ArrAll = Split(Replace(List, " ", ""), ",")
    For i = LBound(ArrAll) To UBound(ArrAll)
        ArrRange = Split(ArrAll(i), "-")
        If UBound(ArrRange) > 0 Then
            For ii = ArrRange(LBound(ArrRange)) To ArrRange(UBound(ArrRange))
                Expand = Expand & ii & ","
            Next ii
        Else
            Expand = Expand & ArrAll(i) & ","
        End If
    Next i
    Expand = Left(Expand, Len(Expand) - 1)
End Function

 

[Rick Rothstein]

See my mini-blog article here for code that does this...

Return individual page numbers from a list of pages and ranges (e.g., 1,5,9-12,20-18)

 

[lneidorf]

Thank you both.

I know this is remedial, but how do I call these functions and deploy them against a range? My impression is that I can enter them just like any built-in function, but that doesn't seem to be working for me. For example, with Rick's function, I am getting "1" as a result. I've played around with cell formatting to no avail.

Thanks!

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B
1	1-4, 6, 8-10	1,2,3,4,6,8,9,10
Sheet7

Cell Formulas
Range	Formula
B1	=expand(A1)

 

[Rick Rothstein]

For example, with Rick's function, I am getting "1" as a result. I've played around with cell formatting to no avail.

What formula did you put in the worksheet cell and, if you referenced any cells within the formula, what was contained in those cells?

 

[lneidorf]

For some reason, Andrew's works for me while Rick's does not. Rick, I've got my original values in col A, and in B2 i entered "=PagesToPrint(A2)". The result is "1".

 

[Andrew Poulsom]

Rick's function returns an array. As he says, you need to make a change if you want it to return a comma delimited string.

 

[Rick Rothstein]

For some reason, Andrew's works for me while Rick's does not. Rick, I've got my original values in col A, and in B2 i entered "=PagesToPrint(A2)". The result is "1".

I figured out what went wrong for you... you stop reading at the code and did not read the parts underneath where it told you to change one line of code if you wanted a comma separated list rather than an array of values. Here is the code you should be using (the red highlighted line of code is what was changed to make the function output the comma separated list)...

Function PagesToPrint(sInput As String) As Variant
  Dim X As Long, Z As Long, Temp As String, sNumbers() As String, sRange() As String
  If sInput Like "*# #*" Then GoTo Bad
  sInput = Replace(sInput, " ", "")
  If sInput Like "*[!0-9,-]*" Or sInput Like "*[,-][,-]*" Or _
     Not sInput Like "*#" Or Not Val(sInput) Like "[1-9]*" Then GoTo Bad
  sNumbers = Split(sInput, ",")
  For X = 0 To UBound(sNumbers)
    If sNumbers(X) Like "*-*" Then
      If sNumbers(X) Like "*-*-*" Then GoTo Bad
      sRange = Split(sNumbers(X), "-")
      sNumbers(X) = ""
      For Z = sRange(0) To sRange(1) Step Sgn(sRange(1) - sRange(0) + 0.1)
        sNumbers(X) = sNumbers(X) & "," & Z
      Next
      sNumbers(X) = Mid(sNumbers(X), 2)
    Else
      sNumbers(X) = Val(sNumbers(X))
    End If
  Next
  PagesToPrint = Join(sNumbers, ",")
  Exit Function
Bad:
  PagesToPrint = Array()
  MsgBox """" & sInput & """" & vbLf & vbLf & "The specified range of values is incorrectly formed!", vbCritical
End Function

 

[lneidorf]

That was it. I caught that after re-reading the post. As always, life is a reading problem.

Thank you both for your time!