How to bring two column values(text data type) in single column?

[PMSR]

I have 3 columns A , B and C. Column A having Item IDs, Column B having Items (Item1, Item 2 and Item 3), Column C having Items ( Item 4, Item 5 and Item 6). 

How can I merge both columns B & C and make it 6 rows with respective Item ID?

[Arul · Accepted solution]

@PMSR ,

I assume your data would be like the below,

To bring the item values from both columns to single, I select the column B and replace blank with some temporary character like the below,

If you look at the M code in the formula bar, you can see that "" has been replaced with text value "a".
In the next step, we will replace that with Column C for the each row by replacing "a" in the formula for each row by using each condition.

Finally, If you don't need the Column C then you can remove that one as well.

Thanks,

Arul