vba 如何使用 VB 或公式从 Excel 单元格中删除空白换行符？

How can I remove blank line breaks from an excel cell with VB or a formula?

提问by Mohamad

I have many cells where data is broken up into many lines; some of those lines are blank. Is there a VB function or formula to remove blank line breaks in Excel cells, or all line breaks?

我有很多单元格，其中数据被分成多行；其中一些行是空白的。是否有 VB 函数或公式可以删除 Excel 单元格中的空白换行符或所有换行符？

回答by Jacob G

Substitute in a cell for linefeed seems to work - =Substitute(A1, CHAR(10), "")

在单元格中替换换行符似乎有效 - =Substitute(A1, CHAR(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.

我知道这篇文章很旧，但我找不到任何可以做到这一点的东西。所以我终于把这个从所有论坛放在一起。

Select CELL or Range and 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

If you Just want ALL line Breaks gone use this.

如果您只想让所有换行符消失，请使用它。

    Sub RemoveLineBreaks()
    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

            strNewVal = Replace(strNewVal, vbLf, " ")

            If strNewVal = strOldVal Then Exit Do
                strOldVal = strNewVal
            Loop

            If rngCel.Value <> strNewVal Then
                rngCel = strNewVal
            End If
        End If
        rngCel.Value = Application.Trim(rngCel.Value)
    Next rngCel
    Application.ScreenUpdating = True
End Sub

回答by Jeremy Young

Use the formula clean e.g =clean(A1). This removes all non-printing characters, and works in Mac versions of excel.

使用公式clean 例如=clean(A1)。这将删除所有非打印字符，并适用于 Mac 版本的 excel。

回答by RonnieDickson

If this does not need formulas -

如果这不需要公式 -

Add a new column immediately to the right of your data (touching the last column). Insert into this column the numbers 1, 2, 3, etc, to the last row of your data, indicating the original order of the data.

立即在数据右侧添加一个新列（触摸最后一列）。将数字 1、2、3 等插入此列到数据的最后一行，表示数据的原始顺序。

Sort the data on any field except this new column. All the blank rows will be sorted to the top or bottom of the data. Delete all the contiguous blank rows. Sort on the column you added to retain the original data row order. Delete the column you added.

对除此新列之外的任何字段上的数据进行排序。所有空白行将被排序到数据的顶部或底部。删除所有连续的空白行。对添加的列进行排序以保留原始数据行顺序。删除您添加的列。