Extracting all numbers from a specific numerical pattern

[AdrianKittlety]

Hope someone can help, I have search for a solution to this but am struggling to find one.

I have a field with a string of numbers seperated by commas and hypens EG:

1,2,3,4,7-12,14,15,17,20-25,28,30

I want the result to be a list of numbers, comma seperated, but with the hypen replaced by the numbers in the range between the 2 numbers each side of the hypen, so example above becomes:

1,2,3,4,7,8,9,10,11,12,14,15,17,20,21,22,23,24,25,28,30

The numbers are week numbers, so will go up to a maximum of 52.

Any help appreciated.

[DataNath · Accepted solution]

Hey @AdrianKittlety, here's one way you could go about this.

What's happening here is we're:

1) Splitting the numbers into a record each

2) Separating those containing a hyphen i.e. the records of interest that we need to create the range for

3) Finding the start (left side) of the range, and the end (right side) of the range

4) Using Generate Rows to generate all the numbers between the range start and end

5) Union the new list of numbers to the numbers that were already fine (i.e. not part of a hyphenated range) from the original input

6) Sorting then concatenating our values

If you really wanted you could likely handle this in just a couple of tools by writing some heavier statements in the Generate Rows to do the range lookups within that, but I think this workflow keeps things easier to understand. Hope this helps!

Edit: Couldn't help myself - here's the shorter version if you're curious where you just do the parsing of the range start/end within the Generate Rows tool itself:

After that just convert the [RowCount] column to a string (so that it can be concatenated) and rename it and Summarize the same as above.

[AdrianKittlety · Accepted solution]

Thanks DataNath

Great solution and works as expected, I like the shorter version so have used that in my workflow. Thanks Again!