How to Remove Line Break in String

Question

I want to Remove the Line Break from the string if my string Ends with Line Break  Sub linebreak myString      If Len myString   lt  gt  0 Then         If Right  myString  1    vbCrLf Or Right  myString  1    vbNewLine Then myString   Left  myString  Len myString  - 1      End If End Sub

User · Answer

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*$".

User · Answer

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

User · Answer

Private Sub Form Load       Dim s As String      s    test   amp  vbCrLf  amp   this   amp  vbCrLf  amp  vbCrLf     Debug Print  s  amp  Len s        s   Left s  Len s  - Len vbCrLf       Debug Print  s  amp  Len s   End Sub

User · Answer

Replace yourString  vbNewLine          vbTextCompare

User · Answer

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  gt  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

User · Answer

mystring   Replace mystring  Chr 10

User · Answer

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  amp  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  lt  gt  strNewVal Then                 rngCel   strNewVal             End If          rngCel Value   Application Trim rngCel Value          End If     Next rngCel     Application ScreenUpdating   True End Sub

User · Answer

Try with the following line   CleanString   Application WorksheetFunction Clean MyString

User · Answer

just this   str   Replace str  vbCrLf

User · Answer

vbCrLf and vbNewLine are actualy two characters long  so change to Right  myString  2   Sub linebreak myString      If Len myString   lt  gt  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

User · Answer

I hope this ll do   or else   may ask me directly  x000D   x000D     x000D  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  amp  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  lt  gt  strNewVal Then                 rngCel   strNewVal             End If          rngCel Value   Application Trim rngCel Value          End If     Next rngCel     Application ScreenUpdating   True End Sub x000D   x000D   x000D

User · Answer

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   amp  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

User · Answer

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    quot Hello quot   amp  vbCrLf     myString   removeLineBreakIfAtEnd myString      MsgBox   quot Here is the resulting string    quot   amp  myString  amp   quot   quot   End Sub

User · Answer

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

[excel] How to Remove Line Break in String

Examples related to excel

Examples related to vba