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!

You could write a VBA function to step through each cell in each column (except the last column) and insert the appropriate number of cells to the left of the current cell based on the value of the current cell. You could also start in the right-most column and determine whether to copy & paste values from cells to the left. Give it a try and let use know if you get stuck. — Nicholas Hunter, Apr 18 '21 at 16:24

score 1 · Answer 1 · edited Apr 20 '21 at 16:10

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:

solution

edited Apr 20 '21 at 16:10

Adrian Mole

answered Apr 19 '21 at 06:23

Aniruddha Kanere

1 Answers1