Separating Values in Columns, Keeping Data in Rows

[seejaydub]

I want to separate all the values in the fifth column, into a row for each, yet have the data in all other columns be filled into each row. I hope I am asking this question clearly. Is this possible?

[bluesky63]

Hi,

Can be achieve using Power Query

Please Login or Register  to view this content.

[Norie]

Try this.

1 Select the data.

2 Goto Data>Get & Transform Data.

3 Select From Table/Range, you should now be in Power query

4 Right click the 5th columns header and select Split Column>By delimiter...

5 Select comma as the delimiter.

6 Under Advanced Options select Split into Rows.

7 Click Ok.

8 Click Close & Load to close Power Query and return to Excel.

Note, this will add a header row to your data.

To remove that you'll need to convert the table to a range.

[mehmetcik]

That is so easy with power Query.

Select one cell in your data

Select the "Home tab", Select "Insert" and then Table Your Data is now in a Table.

Select one cell in your Table

Select the "Data tab", Select "From Table/Range" on the left hand side of the Ribbon. Your Data is now in Power Query.

Select a cell in column 5

Click on "Split to Column", Click on "By Delimiter", Click on "Advanced" and Select "Row" to Split into rows and then Click on "Ok"

Finally Click on Close and Load on the left of the Ribbon.

[seejaydub]

This worked. Thanks.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[seejaydub]

Oh, ok. Thanks for the heads up.