Extract line-by-line AS DISPLAYED from a single cell

[piers_morgan]

Hi everyone,

I've seen similar questions, but no answers.

Imagine cell A1 contains 100s of words, randomly entered by an end user. Cell A1 is wrapped text. Sometimes all text is visible, other times not if there is lots and lots of text.

So as more text is entered, so too does the number of LINES inside cell A1 increase.

When no more text is entered, is there then a way to achieve this:-
Is there a way to get what is DISPLAYED on screen as line 1 to populate cell B1?
Then what is DISPLAYED on screen as line 2 to populate cell B2?
Etc...?

I say 'DISPLAYED' in capitals because I appreciate people's help but there is no point coming back with solutions using 'CHAR(10)' because breaks in lines will be entirely random. And this is where all other attempts I've seen at answering fizzle out.

Reason: for reporting / newsletter purposes, giving control to the end user to type as much as they want into a userform. the userform will put this into cell A1 for transforming into a neat PDF report in specific locations without spilling outside of designated ‘zones’ for print purposes. Knowing just how many lines there are means I can write code to achieve what I need.

Any help much appreciated!

Edit: even lines that cannot be seen (if too much text for screen display), should still be able to be extracted. THANK YOU

 

[Jaafar Tribak]

Which font are you using in cell A1 or what font size ? if cell A1 font remains unchanged we could probably find a workaround .