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

piers_morgan

New Member
Joined
Sep 3, 2003
Messages
20
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 :)
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Which font are you using in cell A1 or what font size ? if cell A1 font remains unchanged we could probably find a workaround .
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top