How to remove data within a multi-line cell that comes after a few line breaks

Question

A few line breaks separate the content I need from the stuff I don't need in each cell of an excel spreadsheet I have. Using a formula that instructs each cell's line breaks and anything that follows them to be eliminated, I would like to remove the undesirable information. For instance:

Three blind mice are shown in cell A1. Observe their running technique. The farmer's wife was pursued by everyone. With a carving knife, she removed their tails. Have you ever witnessed a scene involving three blind mice?

Star Bright and Light Little Star, Twinkle, Twinkle I'm a Little Teapot, Jack and Jill

In the above example, how can I remove the line breaks and the list of nursery rhymes, all I want to keep are the lyrics at the top?

Answer 1

• find() return the starting position of a string with a string
• char(10) is the "line feed" in asci (10) getting converted to the character which can be searched
• LEFT() simply returns the left part of the string up to the entrance.

so.

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

Do this in A2 then copy and paste values over your original data if you truly no longer need it. Or reference A2... and keep the original values.

you may want:

 =LEFT(A1,FIND(CHAR(10),A1)-1)

To remove the extra space (carriage return ?) as well.

I think Excel just uses char(10) and not both char(10)+char(13) carriage return