How to Remove Line Break in String
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
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)
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)
Try with the following line:
CleanString = Application.WorksheetFunction.Clean(MyString)