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

0 votes
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?
Jan 26, 2023 in Others by Kithuzzz
• 38,010 points
491 views

1 answer to this question.

0 votes
  • 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

Reference: https://superuser.com/questions/370066/how-to-search-for-newline-or-linebreak-characters-in-excel

answered Jan 26, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers

How to run commands within fabric pods through a batch script?

I am trying to write a batch ...READ MORE

May 30, 2020 in Others by anonymous
• 120 points
766 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
6,139 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
719 views
0 votes
1 answer

How to create an HTML button that acts like a link

To answer your doubt, the plain HTML ...READ MORE

answered Feb 17, 2022 in Others by Aditya
• 7,680 points
3,088 views
0 votes
1 answer

Excel formula to remove comma, spaces, period and add a text

The steps to accomplish this are as ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
1,748 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,163 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,393 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
686 views
0 votes
1 answer

Excel: How to analyze data in a table that contains multivalue cells

 The below formula will create a unique ...READ MORE

answered Jan 10, 2023 in Others by narikkadan
• 63,420 points
200 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
3,017 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP