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:-

Restaurant	Cuisine	Countries
ABC	Chinese	Australia, Germany, France
PQR	Korean	US, Australia
XYZ	American	India, UK, Spain

<tbody>
</tbody>

Restaurant	Cuisine	Country
ABC	Chinese	Australia
ABC	Chinese	Germany
ABC	Chinese	France
PQR	Korean	US
PQR	Korean	Australia
XYZ	American	India
XYZ	American	UK
XYZ	American	Spain

<tbody>
</tbody>

Thank you.

 

[sandy666]

with PowerQuwery

Restaurant	Cuisine	Countries		Restaurant	Cuisine	Countries
ABC	Chinese	Australia, Germany, France		ABC	Chinese	Australia
PQR	Korean	US, Australia		ABC	Chinese	Germany
XYZ	American	India, UK, Spain		ABC	Chinese	France
				PQR	Korean	US
				PQR	Korean	Australia
				XYZ	American	India
				XYZ	American	UK
				XYZ	American	Spain

[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]