Extract Lines into Separate Rows

[pto160]

I am on the latest Excel 365. How can I extract lines into separate rows? Some lines will have a different number of lines, Some for example will have 3, 2 or 1.

Book1
	A
1	Dog Cat Snake
2	Boat Car
3	House
4
5	What I like
6
7	Dog
8	Cat
9	Snake
10	Boat
11	Car
12	House
Sheet1

 

[Holger]

try this one please

Let Function - Split Col and Group by Sum row^Mcol.xlsx
	O
62	Dog Cat Snake
63	Boat Car
64	House
65
66	Result
67	Dog
68	Cat
69	Snake
70	Boat
71	Car
72	House
Test

Cell Formulas
Range	Formula
O67:O72	O67	=VSTACK(TRANSPOSE(TEXTSPLIT(O62,CHAR(10))),TRANSPOSE(TEXTSPLIT(O63,CHAR(10))),TRANSPOSE(TEXTSPLIT(O64,CHAR(10))))
Dynamic array formulas.

 

[Peter_SSs]

Try the simpler ..

25 02 08.xlsm
	A
1	Dog Cat Snake
2	Boat Car
3	House
4
5	What I like
6
7	Dog
8	Cat
9	Snake
10	Boat
11	Car
12	House
pto160

Cell Formulas
Range	Formula
A7:A12	A7	=TEXTSPLIT(TEXTJOIN(CHAR(10),1,A1:A3),,CHAR(10))
Dynamic array formulas.

 

[pto160]

Thank you so much to everyone for your help. These solutions work great. This will save me a lot of time.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

These solutions work great.

The first one would be a bit onerous if the original data was 100 rows though!

Having said that, if your data is very large, mine could fail as TEXTJOIN has a limit of 32,767 characters.

 

[alansidman]

One more option is with Power Query

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

 

[pto160]

alansidman, thanks so much for the power query option as well.