excel - 删除最后一位数字后的字符

Question

我正在使用的代码是将列中的数字格式化为一个字符串，数字用逗号分隔。在一定程度上运作良好。例如。我“连接”成逗号分隔字符串的范围有 398 个可能的单元格(即 A2:A400 )，例如数据从 A300:A400 开始我得到一个完美的字符串，但如果它来自例如A300:A370 ，然后我在最后一个数字后得到 30 个逗号 ( , )。例子:
A300:A400 = ...-0.12345,0.34232,0.221312,0.231132A300:A370 = ...-0.3345,0.014332,0.0021,-0.120031,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
因此，代码会在 A370 之间提取空白文件。和 A400并添加逗号。我不希望在最后一个数字之后有任何逗号，因为范围 A300-A400 就是这种情况。 .请指教。谢谢

Sub Coltocommadelimitstring()
Dim rng As Range
Dim InputRng As Range, OutRng As Range

Set InputRng = ThisWorkbook.Sheets(1).Range("A2:A400")
Set OutRng = ThisWorkbook.Sheets(1).Range("D2")

outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & "," & rng.Value
End If
Next
OutRng.Value = outStr
End Sub

Answer 1

以下公式将为您提供所需的结果，只需在单元格 D2 中输入:

=TEXTJOIN(",",TRUE,A2:A400)

Textjoin 像连接一样工作，但可以有一个分隔符作为参数，它还可以让您忽略空白单元格，第一个参数是分隔符，第二个是忽略空格的标志，第三个是范围。

TEXTJOIN 仅适用于 Office 365 订阅者，一种可能的替代方法是构建您的 UDF，如下所示，这将允许您在没有 Office 365 订阅的情况下使用上面的公式:

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
    If ignore_empty And IsEmpty(cell.Value) Then
        'nothing
    Else
        compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
    End If
Next
TEXTJOIN = compiled
End Function

作为 TEXTJOIN 的替代方案，正如评论所建议的那样，您可以添加另一个检查以查看 rng.Value = ""在将其附加到字符串之前，如下所示:

Sub Coltocommadelimitstring()
Dim rng As Range
Dim InputRng As Range, OutRng As Range

Set InputRng = ThisWorkbook.Sheets(1).Range("A2:A400")
Set OutRng = ThisWorkbook.Sheets(1).Range("D2")

outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        If rng.Value <> "" Then outStr = outStr & "," & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub