Expand range of numbers

[dmingle]

I have a situation that I need help with.
In cell “A2” I have the following “789-793”. I would like to expand the sequence of numbers anytime a “-“ is present by using the 789 as the start (in this example) of the sequence and then using 793 as the end (in this example) of the sequence. I would like for the output to show up in “B2” and I would like the output to be separated by commas. For example, “789, 790, 791, 792, 793”.
There are various grouping of numbers all located in column “A:A” so if using VBA I would like to have a loop until all output is created in “B:B”.
I appreciate your help in advance.

 

[Rick Rothstein]

Question: The code is made to process multiple cells in a column (outputting the result to the cell in the next column for each processed cell in the column; however, you show your desired result to go down a column which would make the original intent of the code impossible to do for multiple cells in a column... are you only processing one cell then? Or, if more than one cell, are your processing multiple cells in a row? Either way, the code will need to be modified for whatever it is you are now trying to do.

 

[darrow]

Question: The code is made to process multiple cells in a column (outputting the result to the cell in the next column for each processed cell in the column; however, you show your desired result to go down a column which would make the original intent of the code impossible to do for multiple cells in a column... are you only processing one cell then? Or, if more than one cell, are your processing multiple cells in a row? Either way, the code will need to be modified for whatever it is you are now trying to do.

Hi Rick,

I am processing many cells all in a single column
For example in column 'A'
So as an example, column A's different cells could be:

A1: 555-567
A2: 789-793
A3: 004-009

and so on.

So as mentioned i'd like to have the expanded list all in another single column.

 

[Rick Rothstein]

I am processing many cells all in a single column
For example in column 'A'
So as an example, column A's different cells could be:

A1: 555-567
A2: 789-793
A3: 004-009

and so on.

So as mentioned i'd like to have the expanded list all in another single column.

Instead of putting your numbers down Column A, put them across Row 1 starting in cell A1 (I can do that in code if you really want, but it seems to make more sense to just put them where the series will be expanded directly below them) then run this macro...

Sub ExpandSeries()
  Dim X As Long, CG As Variant, Rng As Range, Cell As Range
  Dim Delim As String, Series As String, CommaGroups() As String, DashGroups() As String, Ser() As String
  Set Rng = Range("A1", Range("A1").End(xlToRight))
  For Each Cell In Rng
    CommaGroups = Split(Cell.Value, ",")
    If Left(Cell.Value, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
    For Each CG In CommaGroups
      DashGroups = Split(CG, "-")
      For X = DashGroups(0) To DashGroups(UBound(DashGroups))
        Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))
      Next
    Next
    Ser = Split(Mid(Series, 2), ",")
    Cell.Offset(1).Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)
    Series = ""
  Next
End Sub

 

[darrow]

Instead of putting your numbers down Column A, put them across Row 1 starting in cell A1 (I can do that in code if you really want, but it seems to make more sense to just put them where the series will be expanded directly below them) then run this macro...

Sub ExpandSeries()
  Dim X As Long, CG As Variant, Rng As Range, Cell As Range
  Dim Delim As String, Series As String, CommaGroups() As String, DashGroups() As String, Ser() As String
  Set Rng = Range("A1", Range("A1").End(xlToRight))
  For Each Cell In Rng
    CommaGroups = Split(Cell.Value, ",")
    If Left(Cell.Value, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
    For Each CG In CommaGroups
      DashGroups = Split(CG, "-")
      For X = DashGroups(0) To DashGroups(UBound(DashGroups))
        Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))
      Next
    Next
    Ser = Split(Mid(Series, 2), ",")
    Cell.Offset(1).Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)
    Series = ""
  Next
End Sub

Hi rick, while you're approach does look more user friendly, i require for the output to actually be in a single column, because i will be using the column to do other data manipulations, and having it all in one column wtih each number in a different cell. This would make everything much easier

 

[Rick Rothstein]

Hi rick, while you're approach does look more user friendly, i require for the output to actually be in a single column, because i will be using the column to do other data manipulations, and having it all in one column wtih each number in a different cell. This would make everything much easier

Oh, you wanted everything in one column... okay, no problem... just put all your ranges in cell A1, comma separated and use the macro below. For example, you showed this in Message #12...

A1: 555-567
A2: 789-793
A3: 004-009

Instead of putting that in three cells, just put this in cell A1 instead...

A1: 555-567,789-793,004-009

and then run this macro...

Sub ExpandSeries()
  Dim X As Long, CG As Variant, Delim As String, Series As String
  Dim CommaGroups() As String, DashGroups() As String, Ser() As String
  CommaGroups = Split(Range("A1").Value, ",")
  For Each CG In CommaGroups
    If Left(CG, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
    DashGroups = Split(CG, "-")
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
      Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))
    Next
  Next
  Ser = Split(Mid(Series, 2), ",")
  Range("A2").Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)
End Sub

 

[darrow]

Hi rick, maybe i should have clarified im sorry,

So i'd like the raw data to be as is, because I'd like to have it all there displayed normally, and then say the expanded all in column B. Column A would only be used for reference, so doesnt need to specifically be tied in with column B, just something to look at.

Example:

Column A:

A1 is 444-446
A2 is 555-559
A3 is 666-667

etc

Column B will be
B1 is 444
B2 is 445
B3 is 446
B4 is 555
B5 is 556
B6 is 557
B7 is 558
B8 is 559
B9 is 666
B10 is 667

 

[Rick Rothstein]

So i'd like the raw data to be as is, because I'd like to have it all there displayed normally, and then say the expanded all in column B. Column A would only be used for reference, so doesnt need to specifically be tied in with column B, just something to look at.

Okay, see if this macro works for you...

Sub ExpandSeries()
  Dim X As Long, CG As Variant, Delim As String, Series As String
  Dim CommaGroups As Variant, DashGroups() As String, Ser() As String
  CommaGroups = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
  For Each CG In CommaGroups
    If Left(CG, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
    DashGroups = Split(CG, "-")
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
      Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))
    Next
  Next
  Ser = Split(Mid(Series, 2), ",")
  Range("B1").Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)
End Sub

 

[darrow]

Hi, its almost perfect!

Im seeing ranges such as

[TABLE="width: 128"]
<colgroup><col></colgroup><tbody>[TR]
[TD]05900 - 05999[/TD]
[/TR]
[TR]
[TD]06300 - 06399[/TD]
[/TR]
[TR]
[TD]06500 - 06699[/TD]
[/TR]
[TR]
[TD]06730 - 06999

are expanded with two leading zeros instead of 1 though, for example:

005900
005901, etc
while it should be as
05900, 05901[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hi, its almost perfect!

Im seeing ranges such as

[TABLE="width: 128"]
<tbody>[TR]
[TD]05900 - 05999[/TD]
[/TR]
[TR]
[TD]06300 - 06399[/TD]
[/TR]
[TR]
[TD]06500 - 06699[/TD]
[/TR]
[TR]
[TD]06730 - 06999

are expanded with two leading zeros instead of 1 though, for example:

005900
005901, etc
while it should be as
05900, 05901[/TD]
[/TR]
</tbody>[/TABLE]

That is because this is the first time you showed us that there could be spaces around the dashes (the spaces affected one of the counts for the ranges with leading zeroes). The following code fixes the problem by allowing you to use spaces around the dashes or not... your choice.

Sub ExpandSeries()
  Dim X As Long, CG As Variant, Delim As String, Series As String
  Dim CommaGroups As Variant, DashGroups() As String, Ser() As String
  CommaGroups = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
  For Each CG In CommaGroups
    If Left(CG, 1) = "0" Then
      Delim = ",'"
    Else
      Delim = ","
    End If
    CG = Replace(CG, " ", "")
    DashGroups = Split(CG, "-")
    For X = DashGroups(0) To DashGroups(UBound(DashGroups))
      Series = Series & Delim & Format(X, String(Len(DashGroups(0)), "0"))
    Next
  Next
  Ser = Split(Mid(Series, 2), ",")
  Range("B1").Resize(UBound(Ser) + 1).Value = Application.Transpose(Ser)
End Sub

 

[darrow]

Hi Rick!
I looks like its working perfect, I'll attempt some manipulations with the data and might return with some questions soon.

Thanks alot for your help so far!