Link to home
Start Free TrialLog in
Avatar of cbridgman
cbridgmanFlag for United States of America

asked on

Excel - Remove first line from a multiline cell

I have an Excel spreadsheet with a column in it that contains multiple lines in each cell. The lines in those cells are separated by a line break (when entering data in the cell, pressed Alt + Enter to start new lines). I want to remove the first line in every one of those cells and leave the rest of the lines.

Can you help?

Cell contents appear as follows:

This is line 1
This is line 2
This is line 3

I want the result to look as follows:

This is line 2
This is line 3
Avatar of cbridgman
cbridgman
Flag of United States of America image

ASKER

I would like to do this with a formula rather than a macro. I'll take a macro if I have to but I really don't know how to set those up.
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That almost does the trick but the line breaks disappear as follows:

Original Cell Content

This is line 1
This is line 2
This is line 3

Cell Content After Applying Formula

This is line 2This is line 3

Any way to prevent the removal of line breaks?
You may try this.
Option Explicit

Sub RemoveFirstLine(ByRef SearchRange As Range)
    Dim iterCell As Range
    For Each iterCell In SearchRange.Cells
        iterCell.Value = Right(iterCell.Value, Len(iterCell.Value) - InStr(1, iterCell.Value, Chr(10)))
    Next iterCell
End Sub

Open in new window


You should call this sub with a reference to the range object which has strings to be manipulated.

Example:
Sub StartRemove()
    RemoveFirstLine ActiveWorkbook.ActiveSheet.UsedRange
    RemoveFirstLine ActiveWorkbook.ActiveSheet.Range("B4:B5")
    RemoveFirstLine ThisWorkbook.Worksheets("Sheet1").Range("B4:B5")
End Sub

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of STEPHANIE HARRIS
STEPHANIE HARRIS

How can I manipulate the formula so that I only keep line one and line 2 and 3 are removed?
Stephanie.

it would have better if you opened a question for this. rather than adding the question on someone else question.  anyways,  you can get what you need by this formula

=LEFT(LEFT(A2,LEN(A2)-FIND(CHAR(10),A2,1)),LEN(LEFT(A2,LEN(A2)-FIND(CHAR(10),A2,1)))-FIND(CHAR(10),LEFT(A2,LEN(A2)-FIND(CHAR(10),A2,1)),1))
I apologize! It's my first time posting. The formula is giving me a #VALUE error.
Can you upload the workbook sample because the formula perfectly works based on the example in original post
Sure, attached here is an example. I only want to keep the first line for every cell. Thank you!
workbook-example.xlsx
Try this formula...  

=IFERROR(LEFT(A2, FIND(CHAR(10),A2)-1), A2)
workbook-example.xlsx