vba 如何删除字符串中的换行符

How to Remove Line Break in String

提问by Code Hungry

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

采纳答案by chris neilsen

vbCrLfand vbNewLineare actualy two characters long, so change to Right$(myString, 2)

vbCrLf并且vbNewLine实际上是两个字符长，因此更改为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

回答by Moosli

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 12and carriage return = ASCII 13. Here we see clearly what we all know: the PC comes from the (electric) typewriter

换行 = ASCII 10，换页 = ASCII 12和回车 = ASCII 13。在这里我们清楚地看到了众所周知的：PC来自（电动）打字机

vbLfis Chr (10)and means that the cursor jumps one line lower (typewriter: turn the roller) vbCris Chr (13)means the cursor jumps to the beginning (typewriter: pull back the roll)

vbLf为Chr (10)表示光标向下跳一行（打字机：转动滚轮） vbCr为Chr (13)表示光标跳到开头（打字机：回滚滚轮）

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.

在DOS 中，换行符总是VBCrLf 或 Chr (13) & Chr (10)，无论如何在文件中，但例如也与 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.

另一方面， 在Excel 单元格中，换行符仅为VBLf，即使没有 vbCr，第二行也从第一个位置开始。使用 vbCrLf 然后深入一个单元格。

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:

所以这取决于你从哪里读取和获取你的字符串。如果要删除字符串中的所有 vbLf (Char(10)) 和 vbCr (Char(13))，可以这样做：

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)

回答by Matt Donnan

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:

当您使用 Excel 时，您不需要 VBA 来实现这一点，您可以简单地使用内置的“Clean()”函数，这将删除回车、换行等，例如：

=Clean(MyString)

回答by Rocketq

Clean function can be called from VBA this way:

可以通过这种方式从 VBA 调用 Clean 函数：

Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))

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.

但是，正如此处所写，CLEAN 函数旨在从文本中删除 7 位 ASCII 代码（值 0 到 31）中的前 32 个非打印字符。在 Unicode 字符集中，还有其他非打印字符（值 127、129、141、143、144 和 157）。CLEAN 函数本身不会删除这些额外的非打印字符。

Rick Rothsteinhave written code to handle even this situation herethis 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

回答by decas

just this:

只是这个：

str = Replace(str, vbCrLf, "")

回答by joe

Try with the following line:

尝试使用以下行：

CleanString = Application.WorksheetFunction.Clean(MyString)

回答by user3117021

mystring = Replace(mystring, Chr(10), "")

回答by Mouthpear

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

这将从左侧和右侧删除所有换行符，并删除任何空行。然后修剪它看起来不错。根据您的需要进行更改。如果有兴趣，我还制作了一个删除所有换行符的内容。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

回答by Nate Decker

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.

没有其他答案（有一个例外，请参阅我的编辑）处理有多个尾随回车的情况。如果目标是创建一个类似于“Trim”的函数来删除回车符和空格，您可能希望它足够健壮以删除尽可能多的而不是一个。另外，我建议避免在 VBA 中使用“左”或“右”函数，因为它们在 VB.Net 中不存在。有时可能需要将 Office VBA 宏转换为 VSTO COM 加载项，因此避免使用仅存在于 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.

编辑：我应该澄清一下，这里列出了另一个答案，可以从字符串的两端修剪回车符和空格，但它看起来要复杂得多。这可以相当简洁地完成。

回答by Divyank Sabhaya

Replace(yourString, vbNewLine, "", , , vbTextCompare)

Replace(yourString, vbNewLine, "", , , vbTextCompare)