跳到主要内容

如何在Excel中将列列表转换为逗号分隔列表?

如果要将列数据列表转换为用逗号或其他分隔符分隔的列表,并将结果输出到如下所示的单元格中,则可以通过CONCATENATE函数或在Excel中运行VBA来完成。


使用 TEXTJOIN 函数将列列表转换为逗号分隔列表

Excel TEXTJOIN函数使用特定的分隔符将行,列或单元格区域中的多个值连接在一起。

请注意,该函数仅在 Excel for Office 365、Excel 2021 和 Excel 2019 中可用。

要将列列表转换为逗号分隔列表,请选择一个空白单元格,例如单元格 C1,然后键入此公式 =TEXTJOIN(", ",TRUE,A1:A7) (A1:A7 是将转换为逗号锯齿列表的列, “” 指示您希望如何分隔列表)。 请看下面的截图:


使用CONCATENATE函数将列列表转换为逗号分隔的列表

在Excel中,CONCATENATE函数可以将列列表转换为以逗号分隔的单元格中的列表。 请执行以下操作:

1。 选择一个与列表的第一个数据相邻的空白单元格,例如单元格C1,然后键入此公式 = CONCATENATE(TRANSPOSE(A1:A7)&“,”) (A1:A7 是将转换为逗号锯齿列表的列, “,” 表示要分隔列表的分隔符)。 请参阅下面的屏幕截图:

2。 突出显示 TRANSPOSE(A1:A7)&“,” 在公式中,然后按 F9 键。

3。 除去花括号 { } 从公式中,然后按 输入 键。

现在,您可以看到列列表中的所有值都已转换为单元格中的列表并以逗号分隔。 参见上面的截图。

使用 Kutools for Excel 快速将列列表转换为逗号分隔列表

Kutools for Excel的 合并列或行而不丢失数据 实用程序可以帮助Excel用户轻松地将多列或多行合并为一列/行,而不会丢失数据。 此外,Excel用户可以将这些组合的文本字符串用回车或硬回车换行。


使用VBA将列列表转换为逗号分隔的列表

如果CONCATENATE函数对您来说有点繁琐,则可以使用VBA快速将列列表转换为单元格中的列表。

1。 保持 ALT 按钮并按下 F11 在键盘上打开一个 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块,然后将VBA复制到模块中。

VBA:将列列表转换为逗号分隔的列表

Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
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

3。 点击 运行 按钮或按下 F5 运行VBA。

4。 屏幕上显示一个对话框,您可以选择要转换的列列表。 看截图:

5。 点击 OK,然后弹出另一个对话框供您选择一个单元格。 看截图:

6。 点击 OK,并且列列表中的所有值都已转换为一个单元格中用逗号分隔的列表。

Tips::在上面的VBA中,“,”表示您需要的分隔符,您可以根据需要进行更改。


使用 Kutools for Excel 将列列表转换为逗号分隔列表

您还可以应用 Kutools for Excel 结合 组合列列表的实用程序,并轻松地用逗号分隔每个值。

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

1。 选择要转换为逗号分隔列表的列列表,然后单击 库工具 > 合并和拆分>合并行,列或单元格而不会丢失数据.

2。 在打开的“合并列或行”对话框中,您需要:
(1)检查 合并行 在选项 根据以下选项合并选定的单元格 部分;
(2)在 指定分隔符 部分,检查 其他分隔符 选项,然后键入逗号 , 进入下面的盒子;

3。 点击 Ok 按钮。

现在,您将看到指定列列表中的所有值都合并到一个单元格中,并转换为逗号分隔的列表。

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!


演示:在Excel中将列列表转换为逗号分隔的列表


Kutools for Excel:超过 300 个方便的工具触手可及! 立即开始 30 天免费试用,没有任何功能限制。 立即下载!

反向串联并将一个单元格(逗号锯齿状列表)转换为Excel中的行/列列表

通常,Excel用户可以应用 文本到列 功能可以将一个单元格拆分为多列,但没有直接方法将一个单元格转换为多行。然而,Kutools for Excel 分裂细胞 实用程序可以帮助您轻松完成此操作,如下图所示。



相关文章:

Comments (34)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Hi Eric,
Thanks for you feedback. Since the TEXTJOIN is a rather new function, it was not there when we wrote the instruction. I will include the function in the article. Thanks so much.
Amanda
This comment was minimized by the moderator on the site
Major time saving technique - thank you!
This comment was minimized by the moderator on the site
Doesn't work, too many arguments in function.
This comment was minimized by the moderator on the site
This was a life saver! Thanks
This comment was minimized by the moderator on the site
Can someone help in converting a cell wish Value

Football, Baseball, Cricket

into


1. Football, 2. Baseball, 3. Cricket
This comment was minimized by the moderator on the site
Hi ADMINDIVISION,
You can use the Text to Columns (Excel built-in feature) or Split Cells (of Kutools for Excel) to split the cell to three columns or rows, and then apply the Insert Bullets or Numbering feature of Kutools for Excel to quickly insert numbering for the new cells/columns/rows.

Btw, there is an article introducing several solutions to inserting bullets or numberings into cells:https://www.extendoffice.com/documents/excel/950-excel-apply-bullets-numbering.html
This comment was minimized by the moderator on the site
I need to get my numbers to look like this ('1234567', '1234567') instead of ("1234567", "1234567") - any ideas!?
This comment was minimized by the moderator on the site
=SUBSTITUTE(value,CHAR(34),CHAR(39))
This comment was minimized by the moderator on the site
Hi,
There are no double quotas or quotas in the conversation results with any one of methods in this article.
This comment was minimized by the moderator on the site
Absolutely magic!Thanks!
This comment was minimized by the moderator on the site
Used This one: "Convert column list to comma separated list with VBA", worked perfect for converting rows (1000+) of email addresses into a combined list that my email client works well with. Just changed the "," to "; " and it was good to go. Thanks!
This comment was minimized by the moderator on the site
I would like to know how did you manage to copy the comma separated values from Excel Spreadsheet to Outlook/other main client. The reason being whenever I copy, the only formula gets copied but not the comma separated values. Please support.
This comment was minimized by the moderator on the site
Hi, Ravindran, you shold copy the formula result and the paste it into a cell as value firstly, then copy the pased value to other devices.
https://www.extendoffice.com/images/stories/comments/sun-comment/paste%20as%20value.png?1697765930000
This comment was minimized by the moderator on the site
Hi All, So for a few columns this formula is great, but if you were trying to figure this out on more cloumns more than 100. putting "&" is a waste of time. For me i needed sepration through "," (comma). for that, all you need to do is to separate the file in CSV, Open it on a Notepad, Copy and paste in in Word, then Copy again from Word and paste it on Excel. Hope you like to the suggested, Have a great day. Thank you, Mayank Bhargava
This comment was minimized by the moderator on the site
The VBA helped out so much! I am experiencing an issue if you try to close the pop up box or cancel it, it will show that the vba needs debugged. This also happens if the value is blank. Any ideas on how to fix this? Thanks!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations