Expand Range of Values to Individual Values

[RWeaverAMT]

I have a sheet which contains hyphenated number ranges. For example:

201-203
290-291
301-304

I want to "explode" these ranges into their constituent numbers (in individual cells), like so:

201
202
203
290
291
301
302
303
304

I've found some other threads with solutions posted, but am having issues getting them to work. For example, the following thread gives a VBA solution:

Expand range of numbers

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...

www.mrexcel.com

The VBA solution:

[table="width: 500"]
[tr]
[td]Function Mystr(S As String) As String
Dim X As Long, Z As Long, Start As Long, Finish As Long, Txt As String, Parts() As String
Parts = Split(S)
For X = 0 To UBound(Parts)
Txt = Left(Parts(X), Len(Parts(X)) / 2 - 3)
Start = Right(Left(Parts(X), Len(Parts(X)) / 2), 3)
Finish = Right(Parts(X), 3)
For Z = Start To Finish
Mystr = Mystr & " " & Txt & Z & Txt & Z
Next
Next
Mystr = Trim(Mystr)
End Function[/td]
[/tr]
[/table]

But when I put the code into VBA, then call the function in a cell, I get the following error:

It's strange because I'm copying the code exactly from the thread, and there are responses in the thread saying that the code worked for them. I can only assume that some Excel/VBA version change is at fault, as the solution is a few years old. I've found and tried other, similar solutions, with the same error as a result.

I don't know much VBA at all. Any input is much appreciated. Thank you.

 

[Momentman]

Your VBA code seems to have carried over some html table tags. Without delving into the code, i would say, make sure your code looks like the below image

The code's line must be "Function Mystr...." and it should end with "End Function" (nothing after this)

 

[lrobbo314]

Here is a way using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transform = Table.TransformColumns(Source,{{"Ranges",each 
  let 
    x = List.Transform(Text.Split(_,"-"), Number.From),
    l = {x{0}..x{1}}
  in
    l
}}),
    Expand = Table.ExpandListColumn(Transform, "Ranges")
in
    Expand

Expand
	A	B	C
1	Ranges		Ranges
2	201-203		201
3	290-291		202
4	301-304		203
5			290
6			291
7			301
8			302
9			303
10			304
Sheet7

 

[RWeaverAMT]

Here is a way using Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transform = Table.TransformColumns(Source,{{"Ranges",each
  let
    x = List.Transform(Text.Split(_,"-"), Number.From),
    l = {x{0}..x{1}}
  in
    l
}}),
    Expand = Table.ExpandListColumn(Transform, "Ranges")
in
    Expand

Expand
	A	B	C
1	Ranges		Ranges
2	201-203		201
3	290-291		202
4	301-304		203
5			290
6			291
7			301
8			302
9			303
10			304
Sheet7

Thank you very much. I've got this working with the criteria I gave in my OP.

Now, what if I have a slightly different set of data, such as a column that looks like:

101
201-203
291-291
301-304

Your M Code above gives an error for the cell containing "101":

"There weren't enough elements in the enumeration to complete the operation."

Because the "101" cell doesn't contain any hyphenation, I'm guessing. Is there a way to address this?

Thanks again for your time.

 

[RWeaverAMT]

Your VBA code seems to have carried over some html table tags. Without delving into the code, i would say, make sure your code looks like the below image

The code's line must be "Function Mystr...." and it should end with "End Function" (nothing after this)
View attachment 96718

You are absolutely right. I can't believe I didn't notice the extra tags there. Thank you!

 

[lrobbo314]

This should take care of that issue.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Transform = Table.TransformColumns(Source,{{"Ranges",each try
      let 
         x = List.Transform(Text.Split(_,"-"), Number.From),
         l = {x{0}..x{1}}
      in
         l
    otherwise {_}}}),
    Expand = Table.ExpandListColumn(Transform, "Ranges")
in
    Expand