How to get the first two lines of text from a transferred cell?

Question

I need to take the first two lines of text from a wrapped cell in Excel. For example, a wrapped Excel cell contains the following text:

wrapedtext1 wrappedtext2 wrappedtext3 wrappedtext4 

I only need the first two lines as "wrapedtext1wrappedtext2". Is it possible?

Answer 1

I only need to get the first two lines as "wrapedtext1wrappedtext2". Is it possible.

Yes, maybe it can , but there is a NO SIMPLE way. There are many factors that you will have to consider.

1) Row height in pixels

2) Font Type and Size

3) Line spacing

4) Is the cell paired?

5) Is the cell in Autofit state

6) Is the text all in normal mode or does it have Bold / Italic / Underline characters, etc. etc.

Consider this snapshot

For example, the row height in pixels can be obtained from

Debug.Print Range("A1").Height * (24 / 18)

The font size in the above case can be achieved from this

Debug.Print Range("A1").Font.Size

But the problem is what will happen in the scenario below?

In my opinion, it would be too painful to achieve what you want. It is best to use ALT + Enter to insert line breaks and then to get text.

Followup

Rows are entered into the wrapped cell via vba code. So how to insert data by pressing alt + enter? - 1355 4 hours ago

In such a scenario, you can also use a similar approach.

 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 

NOTE For the above, you will need to record a macro and see what the font, font size and column width are, which can take on particular formatting. Again, you will need to consider the fact that the above example is an unformatted cell on a new sheet. If you are writing in a merged cell or in a formatted cell, you will have to change the code above accordingly, which can be easily done by writing a macro. I also assume that the ZOOM level is set to 100%

SNAPSHOTS

NTN

Sid

Answer 2

Use the FIND function:

= LEFT (B2, (FIND (CHAR (10), B2)))

• FIND searches for a line break character (CHAR (10)
• LEFT gives you the first X characters in B2, right down to the number where it finds a line break

Answer 3

Hi, there are two ways to do this with VBA code.

First way. If lines are separated by space

 Dim avarSplit As Variant 'If separated with a space avarSplit = Split(Worksheets(2).Range("A7").Value, " ") 

the second way, if they are separated by a gap.

 Dim avarSplit2 As Variant 'separated with a break avarSplit2 = Split(Worksheets(2).Range("A8").Value, Chr(10)) 

Then you get an array in which all the lines are separated, and you can just read them ...

Moosli

Answer 4

You can make a complex formula in Excel to achieve this without VBA code.

Assuming multiline text is in A1. If you just need the first two lines, you can do this:

Get the first row:

 =MID(A1,1,IF(2=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1,LEN(A1),SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",2)))) 

Get the second line:

 =MID(A1,SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",1))+1,IF(2=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1,LEN(A1),SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",2)))-SEARCH("@",SUBSTITUTE(A1,CHAR(10),"@",1))) 

Then just use CONCATENATE() to combine the two.

Look at this site, which allows you to specify which line to get: http://www.excelblog.ca/separating-lines-from-multi-line-excel-cell/