☰

MS Excel - 如何删除连接单元格之间的字符

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba microsoft-excel-2007
MS Excel - 如何删除连接单元格之间的字符

我需要连接单元格并在它们之间添加逗号。但连接列中的一些单元格是空的，结果显示它们之间有逗号。如果单元格为空，如何删除逗号？这是一张图片： 在此处输入图片描述

这是链接到上面的图片电子表格。

答案1

通过VBA函数解决：

Function join_ignore_empty(Src As Range) As String
Dim OneCell As Range, tmp As String
For Each OneCell In Src
    If Not CStr(OneCell.Value) = "" Then
        tmp = tmp & "," & CStr(OneCell.Value)
    End If
Next
If tmp <> "" Then
    join_ignore_empty = Mid(tmp, 2)
End If
End Function

和

F1="This is the concatenated text: "&join_ignore_empty(A1:D1)

按公式求解：

F1="This is the concatenated text: "&A1&IF(AND(A1<>"",B1&C1&D1<>""),",","")&B1&IF(AND(B1<>"",C1&D1<>""),",","")&C1&IF(AND(C1<>"",D1<>""),",","")&D1

逻辑：当前一个单元格值和至少一个下一个值（检查连接的值）不为空时，需要逗号。

答案2

假设您的数据从 A1 开始，并且只有 4 列数据，如示例中所示，

使用：

=A1&IF(B1="","",",")&B1&IF(C1="","",",")&C1&IF(D1="","",",")&D1

并向下拖动

根据 Akina 的评论进行编辑（感谢）

=MID(A1&IF(B1="","",",")&B1&IF(C1="","",",")&C1&IF(D1="","",",")&D1,2,10000)

相关内容