Conversion of Embedded lines in a one cell to multiple rows

[Anjankumar2021]

Hi All / @binu_acs 

Kindly help on the below.

Input :

Product ID	Series
880528311 (LG)	0070964046.1.9999 (LG), 0099235825.1.9999 (LG), 0099184094.1.9999 (LG)
990528311 (LG)	0070964046.1.9999 (LG), 0099235825.1.9999 (LG), 0099184094.1.9999 (LG)
770528311 (LG)	0070964046.1.9999 (LG), 0099235825.1.9999 (LG), 0099184094.1.9999 (LG)

Required Output:

Product ID	Series
880528311 (LG)	0070964046.1.9999 (LG)
880528311 (LG)	0099235825.1.9999 (LG)
880528311 (LG)	0099184094.1.9999 (LG)
990528311 (LG)	0080964046.1.9999 (LG),
990528311 (LG)	0089235825.1.9999 (LG)
990528311 (LG)	0091184094.1.9999 (LG)
770528311 (LG)	0060964046.1.9999 (LG),
770528311 (LG)	0079235825.1.9999 (LG)
770528311 (LG)	0069184094.1.9999 (LG)

[Pilsner · Accepted solution]

Hello @Anjankumar2021 

One way to tackle this would be with a text-to-columns tool.

If you configure the tool to split to rows based on a "," delimiter, it should separate out the data into multiple cells.

Then use a data cleanse tool to remove duplicate white space and line break, to get the following output:

I've attached the workflow below, please let me know if you have any questions.

Regards - Pilsner