How to Remove Line Break in String

Summary

str = Replace(str, vbLf, "")

This code takes all the line break's out of the code

if you just want the last one out:

If Right(str, 1) = vbLf Then str = Left(str, Len(str) - 1)

is the way how you tried OK.

Background Info

line feed = ASCII 10, form feed = ASCII 12 and carriage return = ASCII 13. Here we see clearly what we all know: the PC comes from the (electric) typewriter.

vbLf is Chr (10) and means that the cursor jumps one line lower (typewriter: turn the roller)

vbCr is Chr (13) and means the cursor jumps to the beginning (typewriter: pull back the roll)

In DOS, a line break is always VBCrLf or Chr (13) & Chr (10), in files anyway, but e.g. also with the text boxes in VB.

In an Excel cell, on the other hand, a line break is only VBLf, the second line then starts at the first position even without vbCr. With vbCrLf then go one cell deeper.

So it depends on where you read and get your String from. if you want to remove all the vbLf (Chr(10)) and vbCr (Char(13)) in your string, you can do it like this:

strText = Replace(Replace(strText, Chr(10), ""), Chr(13), "")

If you only want t remove the Last one, you can test on do it like this:

If Right(str, 1) = vbLf or Right(str, 1) = vbCr Then str = Left(str, Len(str) - 1)

vbCrLf and vbNewLine are actualy two characters long, so change to Right$(myString, 2)

Sub linebreak(myString)
    If Len(myString) <> 0 Then
        If Right$(myString, 2) = vbCrLf Or Right$(myString, 2) = vbNewLine Then 
            myString = Left$(myString, Len(myString) - 2)
        End If
    End If
End Sub

Try with the following line:

CleanString = Application.WorksheetFunction.Clean(MyString)

As you are using Excel you do not need VBA to achieve this, you can simply use the built in "Clean()" function, this removes carriage returns, line feeds etc e.g:

=Clean(MyString)