I'm not sure how to explain this, but I have two columns that have multiple data words in each cell per column (Notional Data) that need to be combined, but separately.

             Column A                           Column B                Column C
Row 1   Apple; Orange; Pear; Watermelon    Seedless; Sour; Sweet; Red    CAT 1
Row 2   Grape; Watermelon; Kiwi            Green; Sour; Large; Red       CAT 2

I have over 200 rows like this, and I need to convert them to look like this:

                Column A
Row 1   Apple;Seedless;CAT 1
Row 2   Apple;Sour;CAT 1
Row 3   Apple;Sweet;CAT 1
Row 4   Apple;Red;CAT 1
Row 5   Orange;Seedless;CAT1
Row 6   Orange;Sour;CAT 1
Row 7   Orange;Sweet;CAT 1
Row 8   Orange;Red;CAT 1
Row 9   Pear;Seedless;CAT1

After all possible combinations of source row 1 have been expanded, then move on to source row 2.


You need to first of all separate the values, then you can recombine the ones you want.

Try using using the text to columns tool.


and specifying the delimeter as ;

Then use concantenate to assemble the parts you want


Not the answer you're looking for? Browse other questions tagged or ask your own question.