Separating number of lines from a cell

[Danbrix]

Hi

I hve a cell with as many as 100 lines and which can change depending on user input.

I can find how many lines there are using, but I want to separate the lines into different cells.

Is a way to separate number of lines just like we use mid, left and right with number of charaters?

Thanks

 

[jtakw]

So these "lines" are separated by "linefeed", correct?

I can help with a formula solution, if you're interested.

 

[Danbrix]

As the new lines are directly from a user, from a textbox, they can be line feeds as well as end of line. Just to be clear, either
1. It goes to the next line as there is no room for the following word or sentence or
2. It goes to the next line by pressing the enter key within the text box.
Thank you for your help.

 

[jtakw]

Well, as I'm uncertain "exactly" how the Cell is populated, this assumes Every line ends with a linefeed, see how it works for you...

Assuming Original data in A1, Formula in A2 copied down as far as needed:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(CHAR(10)&A$1,CHAR(10),REPT(" ",LEN(A$1)),ROWS(A$2:A2)*25-24),CHAR(10),REPT(" ",LEN(A$1)),ROWS(A$2:A2)*25),LEN(A$1)+ROWS(A$2:A2),LEN(A$1)))

 

[Danbrix]

Thanks jtakw. This works for me.

 

[jtakw]

You're welcome.