Deliminate data based on line breaks

[ukmike007]

Hi All

I have been sent a large spreadsheet of data, that contains within a column a number of options. Each option is within an individual cell, separated by line breaks.

There are between 1 & 20 options on each row.

I need to text-to-columns or similar to split these into individual columns on the same line of the spreadsheet...

Example as below

Many thanks

Mike

E.g.

Current				Output
Entry	Person	City	Fruit Options	Fruit 1	Fruit 2	Fruit 3
1​	John	Oxford	Orange x 2 Apple x 1	Oranges x 2	Apple x 1
2​	Bill	Birmingham	Grapes x 2	Grapes x 2
3​	Simon	London	Lemons x 5 Strawberry x 7 Mango x 3	Lemons x 5	Strawberry x 7	Mango x 3
4​	Mary	Manchester	Pear x 2 Apple x 1	Pear x 2	Apple x 1
5​	Anne	Glasgow	Orange x 2 Melon x 3 Apple x 1	Orange x 2	Melon x 3	Apple x 1

 

[Alex Blakenburg]

Are you happy to use Power Query ?

 

[ukmike007]

I have not used power query before. Managed to find elsewhere that Control + J is the deliminator for line breaks, but would be interested in another solution as I need to next extract data from within the line breaks and analyse it.

 

[Alex Blakenburg]

Have used table before ?
If so I assume that the word current is not in your original data.
(If it is insert a blank row under the word current)
Then click anywhere in your table and hit ctrl+T
Then still in the table on the right side of the menu click Table Design
Then on the left side there is a Table Name box it will mostly likely say Table1, type in something meaningful
(I used tblImport)

Still with the cursor inside the table.
Data > From Table/Range (far left)
You will now be in the Power Query editor
Select the Fruit Options column
Transform > Split Columns (about in the middle) > By Delimiter
Select the settings in the screenshot below and hit ok
Give the query a meaningful name

Home > Close and Load To > either select new worksheet or Existing worksheet and a location

Split

Close & Load To