Only showing text before 5th carriage return

[yalane1103]

Hi All,

First time poster, so please be gentle.

I have a cell that has 30 lines of status updates (broken by carriage returns). I only want to show the first 5 lines of status updates and delete the rest of the text. HELP! I know how to search for carriage return and show a specific amount of characters before or after the return, but I don't want the text to be cutoff mid-word or mid-sentence, so I want it to end at a carriage return. Just formulas please.

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LEFT(A2,FIND("^",SUBSTITUTE(A2,CHAR(10),"^",5))-1)

 

[yalane1103]

Hi & welcome to MrExcel.
How about

=LEFT(A2,FIND("^",SUBSTITUTE(A2,CHAR(10),"^",5))-1)

Hmm, showing #VALUE! error

 

[Fluff]

will you alwasy have at least 5 linefeeds in the cell?

 

[yalane1103]

will you alwasy have at least 5 linefeeds in the cell?

no, not always

 

[Fluff]

Does that formula work for cells with 5 or more linefeeds?
Also what should happen if there are less than 5?

 

[yalane1103]

Does that formula work for cells with 5 or more linefeeds?
Also what should happen if there are less than 5?

oh! so sorry. yes it works when there are more than 5 returns. i thought the cells i was trying it on had more than 5 returns but they didn't.

but to your point, there are cases when there will NOT be 5 returns, and in those cases, i want it to return all the text. thank you!

 

[Fluff]

In that case try

=IFERROR(LEFT(A2,FIND("^",SUBSTITUTE(A2,CHAR(10),"^",5))-1),A2)

 

[yalane1103]

In that case try

=IFERROR(LEFT(A2,FIND("^",SUBSTITUTE(A2,CHAR(10),"^",5))-1),A2)

THANK YOU!!!! that worked perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.