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 |
