Generate rows for values within a range

[JamesHowells1984]

Hi

I have a list of items that are a mixture of single items (e.g. AC10) and a range (e.g. AG211-AG221) - see below. I want to generate a list that shows the single items as they are, but if there is a range, I want a new row for each value that falls between the range. For example for  AG211-AG221, I'd like to generate rows AG211, AG212, AG213 AG214, AG215 etc. I can split these using the text to columns tool and thought I  could use generate rows or the multi-row formula but am not sure if/how this is possible. Any tips would be greatly appreciated!

Thanks in advance

James

[FrederikE]

Hey @JamesHowells1984,

Result:

-> 

Flow:

[JamesHowells1984]

Thanks so much for the quick response. I'm struggling a bit as my dataset has letters in both parts of the range e.g.AG211 - AG221 whereas your example only has letters at the start of the range: AC6-10 so the workflow doesn't quite work with my dataset. Is there an adjustment that can be made to account for the format of my data?

Thanks again

[FrederikE]

Hey @JamesHowells1984,

I duplicated my "Regex" tool to do the same for the second part. Should work now for you. 

[OllieClarke]

@JamesHowells1984 

Here's another way of doing it:

I did assume that the prefix of the numbers will be identical between range endpoints. Then I can parse that out, along with the minimum value, and maximum value (which is set to the same as min if there is no hyphen)

Hope that helps,

Ollie

[JamesHowells1984]

Thank you. The data is in a variety of formats (different lengths, some with additional letters etc) so it's not quite as simple as I initially stated. Your solution provides me with a really good starting point so I'll see if I can develop a work around.

Thanks again.