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.


1 Answer 1


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.