Splitting comma-separated values into fields with limited size

[michael_hartmann1]

I have a column in my data that is a comma-separated list of values. I need to group them into sets that contain no more than a fixed number of characters (in my case 250). The length of each value in a list can vary in length, and I want to break the list right after a comma (not arbitrarily at 250 characters) and pick up again at the next value. 

Here is an example of what I want to do (in this case, assume the limit on the output columns is 20 characters):

Input:

Row 1:  value1, value2, value3, val4, valu5, value6, value 7

Row 2:  val8, val 9, value 10, value11, valu 12, val13, val 14, value15

Output:

Row 1 - Column 1:  value1, value2  -- 14 characters

Row 1 - Column 2:  value3, val4, valu5 -- 19 characters

Row 1 - Column 3:  value6, value 7 -- 15 characters

Row 1 - Column 4:  <null>

Row 2 - Column 1:  val8, val 9 -- 11 characters

Row 2 - Column 2:  value 10, value11 -- 17 characters

Row 2 - Column 3:  valu 12, val13 -- 14 characters

Row 2 - Column 4:  val 14, value15 -- 15 characters

Thanks for any help!

[MarqueeCrew · Accepted solution]

Michael,

I moved your forward with this module.  I did however identify the row ID with the correct contents (example below isn't true to requirements).  My output however doesn't crosstab the data back to a denormalized format.  Take a look at this and let me know if you it gets you where you are trying to be.  

Thanks,

Mark

[michael_hartmann1]

Mark - this is great...I had started down a similar path. Good validation that the approach is the right one.

[michael_hartmann1]

I was able to accomplish this. I hard coded the limit on column size, and I think that Mark's input option would be a good addition. My current workflow is pretty messy, but I'll try to clean it up and share on this thread next week.