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"

Solution 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)

Solution 2

Well the VBA code for a line break like this is Chr10. So you'll need to use this to find the position of the second line break by using the Mid function, and get all the characters before that by using the Left function.

If no one has given a clearer answer I will edit this question later when I have time to test.

EDIT

I noticed just now you said "wrapped text", not that they were line breaks like I assumed. Are the lines being created only by the column size, or do you have actual line breaks?