We help IT Professionals succeed at work.

Excel - Remove first line from a multiline cell

11,793 Views
Last Modified: 2020-05-05
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
Comment
Watch Question

Author

Commented:
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.
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Hakan YılmazProject Controls Lead Engineer
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
How can I manipulate the formula so that I only keep line one and line 2 and 3 are removed?
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
CERTIFIED EXPERT

Commented:
Try this formula...  

=IFERROR(LEFT(A2, FIND(CHAR(10),A2)-1), A2)
workbook-example.xlsx
bfs
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.