StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <blockquote> <blockquote> <p>I only need to get first two lines as 'wrapedtext1wrappedtext2' .Is it possible???</p> </blockquote> </blockquote> </blockquote> <p>Yes it <strong>might</strong> be possible but there is <strong>NO SIMPLE</strong> way to achieve it. There are lot of factors that you will have to consider.</p> <p>1) Row Height in Pixels</p> <p>2) Font Type and Size</p> <p>3) Line Spacing</p> <p>4) Is the Cell Merged?</p> <p>5) Is the cell in Autofit state</p> <p>6) Is all the text in Normal mode or does it have any Bold/Italics/Underline character(s) etc etc</p> <p>Consider this snapshot</p> <p><img src="https://i.stack.imgur.com/OTRbx.png" alt="enter image description here"></p> <p>For example, Row Height in Pixels can be derived from</p> <p><code>Debug.Print Range("A1").Height * (24 / 18)</code></p> <p>Font Size in the above case can be achieved from this</p> <p><code>Debug.Print Range("A1").Font.Size</code></p> <p>But the challenge is what would happen in the below scenario?</p> <p><img src="https://i.stack.imgur.com/bkUcR.png" alt="enter image description here"></p> <p>In my opinion, it would be too much of a pain to achieve what you want. The best part would be to use <code>ALT + Enter</code> to insert line breaks and then retrieve the text.</p> <p><strong>FOLLOWUP</strong></p> <p>The strings are entered into the wrapped cell through vba code. So How will insert data by pressing alt + enter? – 1355 4 hours ago</p> <p>In such a scenario, you can take a similar approach as well.</p> <pre><code>Sub Sample() Dim strg As String strg = "This is a sample" & vbCrLf & _ "sentence which is" & vbCrLf & _ "in Cell A1 and the" & vbCrLf & _ "text is separated" & vbCrLf & _ "with line breaks" With Range("A1") .Columns(1).ColumnWidth = 16.86 .Font.Name = "Calibri" .Font.Size = 11 .Value = strg End With End Sub </code></pre> <p><strong>NOTE</strong>: For the above <strong>you will have to record a macro</strong> and see what is the font, font size and column width that can take a particular formatting. Again, you will have to consider the fact that the example that I have given above is for a non formatted cell in a new sheet. If you are writing to a merged cell or a per-formatted cell then you will have to change the above code accordingly which can be easily achieved by recording a macro. <strong>I am also assuming that the ZOOM level is set to 100%</strong></p> <p><strong>SNAPSHOTS</strong></p> <p><img src="https://i.stack.imgur.com/0yxLs.png" alt="enter image description here"></p> <p>HTH</p> <p>Sid</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload