How to extract a particular line of text from wrapped text cell?

Question

Is it possible to take only the first two lines from a wrapped text cell through VBA code?

For example, a wrapped text cell has value as:

aaaaaaa

bbbbbbb

ccccccc

ddddddd

So the cell contains 4 lines of wrapped text. I need to extract

"aaaaaaaa

bbbbbbbb"

Answer 1

With your text in A1, you can try this formula to extract the first two lines:

 =LEFT(A1,FIND(CHAR(10),A1,FIND(CHAR(10),A1)+2))

You can use =SUBSTITUTE(...,CHAR(10)," ") to replace line breaks with spaces.

If you want to use VBA, you could use:

 split(cells(1,1),chr(10))(0) & " " & split(cells(1,1),chr(10))(1)