Create new Rows

[Danny54]

What is the best way to expand data found in a cell by creating new rows?
In my example I have a row of data that contains 3 values (row Kind)
In Sheet2 the data is expanded into 3 different rows keeping all the other values

Sheet1

Color	Date1	Date2	Date3	Kind	Owner
Red	8/10/2022 15:35	8/10/2022 16:10	8/11/2022 13:57	dog cat bird	Sophia

Sheet2

Color	Date1	Date2	Date3	Kind	Owner
Red	8/10/2022 15:35	8/10/2022 16:10	8/11/2022 13:57	dog	Sophia
Red	8/10/2022 15:35	8/10/2022 16:10	8/11/2022 13:57	cat	Sophia
Red	8/10/2022 15:35	8/10/2022 16:10	8/11/2022 13:57	bird	Sophia

Thanks

 

[Danny54]

figured a way that works

select the Kind column of data
do a control H to bring up a find and change dialog
insert control+j in the find and a comma in the replace
this sets up for the create new rows using the power query editor
next select all the data and turn it into a table by selecting the data tab then From Table / Range - this opens the data in power query editor
select the Kind row of data
select Split Column and do by delimiter
set the delimiter to a comma
select the Advanced options
select Split into rows
this will now create new rows based on each kind
select Close & Load to save the data back to a new sheet

Hope this help anyone else trying to do the same thing

Thanks