Excel remove ONLY trailing carriage returns

bluemarlin

New Member
Joined
Mar 25, 2019
Messages
2
I'm trying to remove only the trailing carriage returns from my data. Sometimes there's 1, sometimes 2 or more. I don't want to remove other line breaks, only the trailing ones. Can anyone kindly assist?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,650
Office Version
365
Platform
Windows
Try this method on a COPY of your data

Code:
Sub RemoveTrailingLineFeed()

    Dim myStr As String, JobIsDone As Boolean, Cel As Range

    For Each Cel In Range("A1:A100")
        If Cel.Value <> "" Then
            myStr = Cel.Value
            JobIsDone = False
            Do Until JobIsDone
                Select Case Right(myStr, 1)
                    Case Chr(13), Chr(10)
                        myStr = Left(myStr, Len(myStr) - 1)
                    Case Else
                        JobIsDone = True
                End Select
            Loop
            Cel = myStr
        End If
    Next Cel
    
End Sub
 
Last edited:

bluemarlin

New Member
Joined
Mar 25, 2019
Messages
2
Thanks so much Yongle, this works perfectly. You have saved me LOTS of time!

Try this method on a COPY of your data

Code:
Sub RemoveTrailingLineFeed()

    Dim myStr As String, JobIsDone As Boolean, Cel As Range

    For Each Cel In Range("A1:A100")
        If Cel.Value <> "" Then
            myStr = Cel.Value
            JobIsDone = False
            Do Until JobIsDone
                Select Case Right(myStr, 1)
                    Case Chr(13), Chr(10)
                        myStr = Left(myStr, Len(myStr) - 1)
                    Case Else
                        JobIsDone = True
                End Select
            Loop
            Cel = myStr
        End If
    Next Cel
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,747
Messages
5,446,255
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top