将范围转换为逗号分隔的字符串

Question

如果我有这样一列：

Col1
abc
def
ghi
jkl

如何将它转换为这样的字符串？：

"abc,def,ghi,jkl"

- David Klempfner

---

非 VBA 方法是使用 & 或 Concatenate 函数。 - Pankaj Jaju

在Office 2016中，可以使用CONCAT和TEXTJOIN函数来连接文本。参考链接：https://blog.crossjoin.co.uk/2016/02/05/new-ways-to-concatenate-text-in-excel-2016-with-concat-and-textjoin/ - brettdj

Answer 1

您可以使用Join()函数将一维数组的所有元素用分隔符连接起来。

下面使用Transpose()函数来形成多维数组（此方法适用于单列或单行）。

Sub Main()
    Dim arr
    arr = Join(Application.Transpose(Range("A2:A5").Value), ",")
    MsgBox arr
End Sub

或者作为UDF

Public Function Merge(r As Range) As String
    Merge = Join(Application.Transpose(r.Value), ",")
End Function

- user2140173

---

这仅适用于单列中的单元格。但不适用于单行中的单元格，如 arr=Join(Application.Transpose(Range("A1:B1").Value), ",")。 - nixda

@nixda 我想你会想要在这种情况下进行两次范围转置。 - user2140173

Answer 2

如果您需要使用更重的机器，可以使用下面答案中提供的解决方案。我曾经遇到过包含数百万单元格的范围类似的挑战。在这种情况下，JOIN会导致崩溃。

请查看以下问题：将Excel范围转换为VBA字符串

我已经测试了以上链接中提供的所有方法。基于JOIN函数的解决方案性能较慢，甚至会导致崩溃。

普通的循环遍历所有单元格比JOIN函数快得多。被接受的答案中的字符串构建器甚至更快。使用字符串构建器，由数百万单元格组成的字符串可以在几秒钟内构建完成。这就是我最终采用的解决方案。

- Przemyslaw Remin

Answer 3

双重转置适用于对单行值进行字符串连接。感谢@user2140173和@brettdj！

debug.print join(Application.Transpose(Application.Transpose(Range("A1:G1").Value)),",")

- Kip Bryan

Answer 4

Public Function COLSASLIST(Rng As Range) As String
    Dim tempStr1 As String
    tempStr1 = Replace(Replace(Join(Application.Transpose(Application.Transpose(Rng.Value)), ","), ",,", ""), ",,", ",")
    If Right(tempStr1, 1) = "," Then tempStr1 = Left(tempStr1, Len(tempStr1) - 1)
    COLSASLIST = tempStr1
End Function

Public Function ROWSASLIST(Rng As Range) As String
    Dim tempStr1 As String
    tempStr1 = Replace(Replace(Join(Application.Transpose(Rng.Value), ","), ",,", ","), ",,", ",")
    If Right(tempStr1, 1) = "," Then tempStr1 = Left(tempStr1, Len(tempStr1) - 1)
    ROWSASLIST = tempStr1
End Function

- Matthew Davison

Answer 5

使用Microsoft 365/Excel2019 (+/-Mac)和Excel for the Web的新动态工作表函数TextJoin()，您可以构建具有以下范围参数的UDF：

• (1) 一列或
• (2) 一行甚至
• (3) 连续的范围输入（例如“A2:C5”）

可选的第二个参数ExcludeBlanks允许省略空值。

函数结果是逗号分隔的列表（对于情况(3)很重要：阅读顺序为按行）。

Function Rng2List(rng As Range, Optional ExcludeBlanks As Boolean = True) As String
    Rng2List = WorksheetFunction.TextJoin(",", ExcludeBlanks, rng)
End Function

请参阅Textjoin函数的帮助文档

- T.M.