Rollover text from one multi-lined cell to other cells

[PrimePorkchop]

I'm having a hard time putting into words what I want to do, so i put it into a demo spreadsheet.

Basically, I have a userform that will "input" a multi-lined entry into one cell. I want to use other cells, on a different sheet, that will allow the text to "roll over" into different columns, thus making it easier to read.

Is there a formula that can accomplish this?

[Glenn Kennedy]

Do you REALLY want 3 results to be in a merged cell: M8:M10. why not one value per cell?

[PrimePorkchop]

If it's possible, yes.

The premise of how I am setting things up is to allow the front-end user to input the data with "ALT+ENTER" creating a new line in the userform... and the back-end user seeing the report, on a different worksheet.

What I don't want to have happen is if the front-end user enters, lets say, 20 lines of information, I dont want to have a cell on the reports side that is 20 lines tall, I'd rather have it spill into 4 different cells that are 5 lines tall (in this case, I just made merge cells to make the height)

Obviously if there's a better way to accomplish this, I'm all ears...what I didn't want to do is limit the front-end user to a certain # of individual textfields... giving them one big text box that they can enter as many lines as they need

I hope all this makes sense

[PrimePorkchop]

I tried displaying all of the results in a gigantic text box, on the back-end, and using "text wrap" ... but unfortunately, the data from the input just continued "down" beyond the scope of the box on the back-end, and didn't actaully "Wrap" -- i am assuming it's because excel sees "ALT+ENTER" text differently than an actual second line.

[Glenn Kennedy]

Hi. Merging the results would require VBA and is well beyond my ability!

Here's an alternative approach:

=TRIM(MID(SUBSTITUTE(CHAR(10)&$C$5,CHAR(10),REPT(" ",125)),125*(1+MOD((ROWS($1:1)-1),3)+3*(COLUMNS($A:A)-1)),125))

in any cell, copy down 3 rows and across, as needed.