How to pull in a range of numbers using a delimiter?

[Charlieswims]

I have a set of data where commas act as the delimiters and periods act as range/wildcard. As an example I need to transform something like this:

column 1          column 2                               column 3

500                  1000, 1002.1004, 1032          3   

Into this:

column 1          column 2                               column 3

500                  1000                                       3

500                  1002                                       3   

500                  1003                                       3 

500                  1004                                       3 

500                  1032                                       3 

What is the most effective way to do this for a large data set? I feel it should be pretty straightforward but was hoping to get some help.

[Luke_C]

Hi @Charlieswims 

Use the text to columns tool, but configure it to split to rows: 

[Kinga]

I think I saw somewhere in forum similar question, but I created the workflow. Please find attached.

[SPetrie · Accepted solution]

Split to rows using the comma and then you can parse out the start and end of the range. Pass those values to a generate rows tool, and then recombine and sort the new items with the main data.

I just made up some data to demonstrate.