Delete text after second carriage return (line break)

[davidbrugge]

Hello,

I have a spreadsheet with text in cells B2:B500. The amount of text varys from cell to cell and most of the text(sentences) have carriage returns/line breaks between them. I am only interested in the text mentioned in the first two lines of the cell and I want everything after that deleted. So the example below shows all the text in cell B2 - I want a formula for cell C2 to keep the red text and delete the blue text.

[COLOR="#FF0000"]This is all the information I need
for my report.[/COLOR]

[COLOR="#0000FF"]All of this text after the second line break
should be ignored or deleted[/COLOR]

The only thing common in each cell between the first sentence and the remaining text is a line break, so I think this is the only reference I can use in the formula.

Any ideas?

Thanks,
David

 

[cerfani]

=mid(b2,1,search(char(10),b2)-1)

 

[davidbrugge]

Thanks for quick reply.

That works for some cells but if the cell does not have a line break then it returns "#value!". Sorry I should have been more clear in my first thread - most of the cells do have line breaks but there are some cells that don't.

Any other suggestions?

 

[cerfani]

Thanks for quick reply.

That works for some cells but if the cell does not have a line break then it returns "#value!". Sorry I should have been more clear in my first thread - most of the cells do have line breaks but there are some cells that don't.

Any other suggestions?

No prob... and yeah you just use iferror...

=iferror(mid(b2,1,search(char(10),b2)-1),b2)

 

[davidbrugge]

Thanks, that works.

 

[cerfani]

no prob