Convert comma separated text in a cell to Rows along with data in other columns

[sachinrohra]

Hi,

How can i convert comma separated text in a cell into multiple rows along with data in other columns. I would want to do this for about 6000 rows and don't want to stay away from using VBA. Can someone please help.

Example:-

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Restaurant[/TD]
[TD]Cuisine[/TD]
[TD]Countries[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Chinese[/TD]
[TD]Australia, Germany, France[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD]Korean[/TD]
[TD]US, Australia[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]American[/TD]
[TD]India, UK, Spain[/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Restaurant[/TD]
[TD]Cuisine[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Chinese[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Chinese[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Chinese[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD]Korean[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD]Korean[/TD]
[TD]Australia[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]American[/TD]
[TD]India[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]American[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]XYZ[/TD]
[TD]American[/TD]
[TD]Spain[/TD]
[/TR]
</tbody>[/TABLE]
















Thank you.

 

[sandy666]

with PowerQuwery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Restaurant[/td][td=bgcolor:#5B9BD5]Cuisine[/td][td=bgcolor:#5B9BD5]Countries[/td][td][/td][td=bgcolor:#70AD47]Restaurant[/td][td=bgcolor:#70AD47]Cuisine[/td][td=bgcolor:#70AD47]Countries[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC[/td][td=bgcolor:#DDEBF7]Chinese[/td][td=bgcolor:#DDEBF7]Australia, Germany, France[/td][td][/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Chinese[/td][td=bgcolor:#E2EFDA]Australia[/td][/tr]

[tr=bgcolor:#FFFFFF][td]PQR[/td][td]Korean[/td][td]US, Australia[/td][td][/td][td]ABC[/td][td]Chinese[/td][td]Germany[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]XYZ[/td][td=bgcolor:#DDEBF7]American[/td][td=bgcolor:#DDEBF7]India, UK, Spain[/td][td][/td][td=bgcolor:#E2EFDA]ABC[/td][td=bgcolor:#E2EFDA]Chinese[/td][td=bgcolor:#E2EFDA]France[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]PQR[/td][td]Korean[/td][td]US[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]PQR[/td][td=bgcolor:#E2EFDA]Korean[/td][td=bgcolor:#E2EFDA]Australia[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]XYZ[/td][td]American[/td][td]India[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]XYZ[/td][td=bgcolor:#E2EFDA]American[/td][td=bgcolor:#E2EFDA]UK[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]XYZ[/td][td]American[/td][td]Spain[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Countries", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Countries")
in
    #"Split Column by Delimiter"[/SIZE]