Excel - Delimiter: Text to columns grouped by value

Question

I have the following column and I am trying to separate the texts into columns by value:

red
red, blue
blue, green
red, blue, green
blue
green

However, when I use the ordinary text to column function I get the following:

red
red     blue
blue    green
red     blue     green
blue
green

I would like the text to be separated into columns and grouped by value:

red
red     blue
        blue     green
red     blue     green
        blue
                 green

Is there a way I can do this?

Thanks!

Answer 1

You can do it by creating a condition check for each text and then using if statement on the condition check.

Please find the solution to your sample problem: