I want to Remove the Line Break from the string if my string Ends with Line Break.
Sub linebreak(myString)
If Len(myString) <> 0 Then
If Right$(myString, 1) = vbCrLf Or Right$(myString, 1) = vbNewLine Then myString = Left$(myString, Len(myString) - 1)
End If
End Sub
I hope this'll do . or else, may ask me directly
Sub RemoveBlankLines()
Application.ScreenUpdating = False
Dim rngCel As Range
Dim strOldVal As String
Dim strNewVal As String
For Each rngCel In Selection
If rngCel.HasFormula = False Then
strOldVal = rngCel.Value
strNewVal = strOldVal
Debug.Print rngCel.Address
Do
If Left(strNewVal, 1) = vbLf Then strNewVal = Right(strNewVal, Len(strNewVal) - 1)
If strNewVal = strOldVal Then Exit Do
strOldVal = strNewVal
Loop
Do
If Right(strNewVal, 1) = vbLf Then strNewVal = Left(strNewVal, Len(strNewVal) - 1)
If strNewVal = strOldVal Then Exit Do
strOldVal = strNewVal
Loop
Do
strNewVal = Replace(strNewVal, vbLf & vbLf, "^")
strNewVal = Replace(strNewVal, Replace(String(Len(strNewVal) - _
Len(Replace(strNewVal, "^", "")), "^"), "^", "^"), "^")
strNewVal = Replace(strNewVal, "^", vbLf)
If strNewVal = strOldVal Then Exit Do
strOldVal = strNewVal
Loop
If rngCel.Value <> strNewVal Then
rngCel = strNewVal
End If
rngCel.Value = Application.Trim(rngCel.Value)
End If
Next rngCel
Application.ScreenUpdating = True
End Submystring = Replace(mystring, Chr(10), "")
Try with the following line:
CleanString = Application.WorksheetFunction.Clean(MyString)
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 tryed OK.
Update:
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) 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 (Char(10)) and vbCr (Char(13)) in your tring, you can do it like that:
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)
No one has ever suggested a RegExp solution. So here is one:
Function TrimTrailingLineBreak(pText)
Dim oRE: Set oRE = New RegExp: oRE.Global = True
oRE.Pattern = "(.*?)(\n|(\r\n)){1}$"
TrimTrailingLineBreak = oRE.Replace(pText, "$1")
End Function
It captures and returns everything up until a single ({1}) trailing new line (\n), or carriage return & new line (\r\n), at the end of the text ($).
To remove all trailing line breaks change {1} to *.
And to remove all trailing whitespace (including line breaks) use oRE.Pattern = "(.*?)\s*$".
Clean function can be called from VBA this way:
Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))
However as written here, the CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.
Rick Rothstein have written code to handle even this situation here this way:
Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
Dim X As Long, CodesToClean As Variant
CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
For X = LBound(CodesToClean) To UBound(CodesToClean)
If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
Next
CleanTrim = WorksheetFunction.Trim(S)
End Function
I had the exact same issue. I made a separate function I can call easily when needed:
Function removeLineBreakIfAtEnd(s As String) As String
If Right(s, 1) = vbLf Then s = Left(s, Len(s) - 2)
removeLineBreakIfAtEnd = s
End Function
I found that I needed to check the last character only and do -2 to remove the line break. I also found that checking for vbLf was the ONLY way to detect the line break. The function can be called like this:
Sub MainSub()
Dim myString As String
myString = "Hello" & vbCrLf
myString = removeLineBreakIfAtEnd(myString)
MsgBox ("Here is the resulting string: '" & myString & "'")
End Sub
I know this post is very old but I could not find anything that would do this. So I finally put this together from all the forums.
This will remove All line breaks from the left and right, and removes any blank lines. Then Trim it all to look good. Alter as you see fit. I also made one that removes All Line Breaks, if interested. TY
Sub RemoveBlankLines()
Application.ScreenUpdating = False
Dim rngCel As Range
Dim strOldVal As String
Dim strNewVal As String
For Each rngCel In Selection
If rngCel.HasFormula = False Then
strOldVal = rngCel.Value
strNewVal = strOldVal
Debug.Print rngCel.Address
Do
If Left(strNewVal, 1) = vbLf Then strNewVal = Right(strNewVal, Len(strNewVal) - 1)
If strNewVal = strOldVal Then Exit Do
strOldVal = strNewVal
Loop
Do
If Right(strNewVal, 1) = vbLf Then strNewVal = Left(strNewVal, Len(strNewVal) - 1)
If strNewVal = strOldVal Then Exit Do
strOldVal = strNewVal
Loop
Do
strNewVal = Replace(strNewVal, vbLf & vbLf, "^")
strNewVal = Replace(strNewVal, Replace(String(Len(strNewVal) - _
Len(Replace(strNewVal, "^", "")), "^"), "^", "^"), "^")
strNewVal = Replace(strNewVal, "^", vbLf)
If strNewVal = strOldVal Then Exit Do
strOldVal = strNewVal
Loop
If rngCel.Value <> strNewVal Then
rngCel = strNewVal
End If
rngCel.Value = Application.Trim(rngCel.Value)
End If
Next rngCel
Application.ScreenUpdating = True
End Sub
Private Sub Form_Load()
Dim s As String
s = "test" & vbCrLf & "this" & vbCrLf & vbCrLf
Debug.Print (s & Len(s))
s = Left(s, Len(s) - Len(vbCrLf))
Debug.Print (s & Len(s))
End Sub
Replace(yourString, vbNewLine, "", , , vbTextCompare)
just this:
str = Replace(str, vbCrLf, "")
None of the other answers (with one exception, see my edit) handle the case where there are multiple trailing carriage returns. If the goal is to make a function similar to "Trim" that removes carriage returns as well as spaces, you'll probably want it to be robust enough to remove as many as there are and not just one. Also, I'd recommend avoiding the use of the "Left" or "Right" functions in VBA since they do not exist in VB.Net. It may be necessary at some point to convert an Office VBA Macro to a VSTO COM Add-In so it's a good habit to avoid the use of functions that only exist in VBA.
Function RemoveTrailingWhiteSpace(s As String) As String
RemoveTrailingWhiteSpace = s
Dim StrLen As Long
StrLen = Len(RemoveTrailingWhiteSpace)
While (StrLen > 0 And (Mid(RemoveTrailingWhiteSpace, StrLen) = vbCr Or Mid(RemoveTrailingWhiteSpace, StrLen) = vbLf) Or Mid(RemoveTrailingWhiteSpace, StrLen) = " ")
RemoveTrailingWhiteSpace = Mid(RemoveTrailingWhiteSpace, 1, StrLen - 1)
StrLen = Len(RemoveTrailingWhiteSpace)
Wend
End Function
Edit: I should clarify that there is another answer listed here that trims carriage returns and white space from both ends of the string, but it looked far more convoluted. This can be done fairly concisely.
Source: Stackoverflow.com