Generate rows for values within a range

Question

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

Obsolete Call Numbers.png

JamesHowells1984 · Answer

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.

OllieClarke · Answer

@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

fillingin.yxmd

FrederikE · Answer

Hey @JamesHowells1984,

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

New Workflow1_updated.yxmd