Find line break in cell, insert in cell below

[Jadatutor]

Hi guys, sorry beginner thanks for any help in advance
I have a column where some cells contain linebreaks. I need the data on the new line to be placed in the next row.

This is how it is currently

Rabbit​

Dog Cat Monkey​

Horse​

and this is how I require it, each one on a new line

Rabbit​

Dog​

Cat​

Monkey​

Horse​

I would prefer to do it with a formula because my vba/macro skills are next to none.
Currently what I'm doing is text to the column, then inserting a space, and then pasting the results transposed.
its taking forever

cheers

 

[JvdV]

A good alternative would be to use PowerQuery and split data to new rows. If formula is a must and data is not too big, maybe try:

Book1
	A
1	Rabbit
2	Dog Cat Monkey
3	Horse
4
5	Rabbit
6	Dog
7	Cat
8	Monkey
9	Horse
Sheet10

Cell Formulas
Range	Formula
A5:A9	A5	=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(CHAR(10),,A$1:A$3),CHAR(10),"</s><s>")&"</s></t>","//s"),ROW(1:1))

 

[Jadatutor]

don't have powerquery. Also fyi got a mac. thank you for trying

 

[JvdV]

Book1
	A
1	Rabbit
2	Dog Cat Monkey
3	Horse
4
5	Rabbit
6	Dog
7	Cat
8	Monkey
9	Horse
Sheet10

Cell Formulas
Range	Formula
A5:A9	A5	=TRIM(MID(SUBSTITUTE(TEXTJOIN(CHAR(10),,A$1:A$3),CHAR(10),REPT(" ",LEN(TEXTJOIN(" ",,A$1:A$3)))),1+(ROW(1:1)-1)*LEN(TEXTJOIN(" ",,A$1:A$3)),LEN(TEXTJOIN(" ",,A$1:A$3))))

 

[Jadatutor]

thank you but this isn't really doing it for me. i have over 65000 lines of data and what you provided is limited to 3 lines. Im so sorry for asking again but sometimes theres 20-30 linebreaks in 1 cell, what i need is a way to find any cell with multiple linebreaks and place every linebreak into the cell below the current cell. thanks in advance

 

[JvdV]

Are all values in the input unique?