Okay, this is a brunt strength approach. There might be a better one utilizing FREQUENCY or some other function. My assumptions are
1. There's never more than three line breaks in a row
2. There are no double line breaks between text
3. The text might contain blanks.
Here's the formula for B2 copied down
Formula:
=IF(LEFT(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""),1)=CHAR(10), MID(IF(RIGHT(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""),1)=CHAR(10),MID(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""),1,LEN(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""))-1),SUBSTITUTE(A2,CHAR(10)&CHAR(10),"")),2,500),IF(RIGHT(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""),1)=CHAR(10),MID(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""),1,LEN(SUBSTITUTE(A2,CHAR(10)&CHAR(10),""))-1),SUBSTITUTE(A2,CHAR(10)&CHAR(10),"")))
Bookmarks