Splitting data without delimiters, referring to other data in the same column

[kwieto]

I see today is is the day of asking for help for me :)

I have values in a column, which I want to split for further processing. Part of them is comma separated, but part of them not. Each value is a string, which can contain two words or more.
What I can assume is that for most cases where there are more than one value in a field, I can find other field in the same column where each of the values will be alone, or separated by a comma.

Initially I tried to split data into rows simply by comma, list all unique values for the column and filter out longest ones (base assumption: if there is no delimiter, then the field after the split should have the longest strings in it), and then use find-replace to add artificial delimiters to the data. But that doesn't work perfectly as sometimes one single value is longer than combination of two others.
As a workaround I do a split of the data as they are now, aggregate results, look what should be corrected and then put it manually on a source list for find-replace before the split and re-run the workflow.
But I look for something less manual, as the current way is not good for broader variety of data.


Below the sample how data are organized currently:

1	Blue Carrot
2	Orange Carrot
3	Very Big and Delicious Green Potato, Orange Carrot
4	Very Big and Delicious Green Potato
5	Very Big and Delicious Green Potato Black Berry
6	Black Berry, Black Potato
7	Blue Carrot Very Big and Delicious Green Potato Black Berry
8	Black Berry Very Big and Delicious Green Potato, Blue Carrot
9	Black Berry Blue Berry

And the output should look like that:

1	Blue Carrot
2	Orange Carrot
3	Very Big and Delicious Green Potato
3	Orange Carrot
4	Very Big and Delicious Green Potato
5	Very Big and Delicious Green Potato
5	Black Berry
6	Black Berry
6	Black Potato
7	Blue Carrot
7	Very Big and Delicious Green Potato
7	Black Berry
8	Black Berry
8	Very Big and Delicious Green Potato
8	Blue Carrot
9	Black Berry
9	Blue Berry

[kwieto]

I did that already for the post with iterative macro