Conversion of Embedded lines in a one cell to multiple rows

Question

Hi All / @binu_acs

Kindly help on the below.

Input :

Product IDSeries880528311 (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 IDSeries880528311 (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 Answer

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