提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

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

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

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

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

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

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

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

Kutools为Excel的 组合列或行 实用程序可以帮助Excel用户轻松地将多个列或行组合成一个列/行而不会丢失数据。 另外,Excel用户可以将这些组合文本字符串换行或回车。 全功能免费试用60天!

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

箭头蓝色右泡 使用CONCATENATE函数将列列表转换为逗号分隔列表


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

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

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

3。 去除大括号 { } 从公式中,按下 输入 键。

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


箭头蓝色右泡 使用VBA将列列表转换为逗号分隔列表

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

1。 保持 ALT 按钮并按下 F11 在键盘上打开一个 Microsoft Visual Basic for Application 窗口。

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,并且列列表中的所有值都已转换为由单元格中的逗号分隔的列表。

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


箭头蓝色右泡 使用Kutools for Excel将列列表转换为以逗号分隔的列表

你也可以申请Excel的Kutools 结合 实用程序组合列列表,并用逗号分隔每个值。

1。 选择要转换为逗号分隔列表的列列表,然后单击 Kutools > 结合.

2。 在打开“组合列或行”对话框中,您需要:

(1)检查 结合行 在选项 根据以下选项组合选定的单元格 部分;

(2)在 指定一个分隔符 部分,检查 其他分离器 选项,并键入逗号 , 插入下面的框中;

3。 点击 Ok 按钮。

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

Kutools for Excel - 包括多个用于Excel的300便捷工具。 全功能免费试用60天,无需信用卡! 现在加入


在这个视频中, Kutools Kutools Plus 选项卡添加 Kutools for Excel。 如果需要,请点击 60天免费试用,不受限制!

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

通常Excel用户可以应用 文本到列 功能将一个单元格拆分为多个列,但没有直接方法将一个单元格转换为多个行。 但是,Excel的Kutools 拆分单元格 实用程序可以帮助您轻松完成下图所示的屏幕截图。 全功能免费试用60天!


相关文章:

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ashlee · 1 years ago
    I need to get my numbers to look like this ('1234567', '1234567') instead of ("1234567", "1234567") - any ideas!?
    • To post as a guest, your comment is unpublished.
      Mike Ranginson · 9 months ago
      =SUBSTITUTE(value,CHAR(34),CHAR(39))
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      There are no double quotas or quotas in the conversation results with any one of methods in this article.
  • To post as a guest, your comment is unpublished.
    Joe sagar · 3 years ago
    Absolutely magic!Thanks!
  • To post as a guest, your comment is unpublished.
    AliciaB · 3 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Mayank Bhargava · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    JohnRIII · 4 years ago
    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!
  • To post as a guest, your comment is unpublished.
    John · 4 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Shalini · 4 years ago
    Very useful macro ..
    Clear explanation.
  • To post as a guest, your comment is unpublished.
    Reema Joseph · 4 years ago
    really helpful post. thank you.
  • To post as a guest, your comment is unpublished.
    TonyPorter · 4 years ago
    You can also paste the column into this online in-browser utility convert.town/column-to-comma-separated-list
    • To post as a guest, your comment is unpublished.
      MoMo · 3 years ago
      [quote name="TonyPorter"]You can also paste the column into this online in-browser utility convert.town/column-to-comma-separated-list[/quote]
      This works like a charm. Thank you very much for sharing it with us. I had 3865 values and Excel weren't showing me all the values in the last line. Perhaps it's a limitation of the Excel.
  • To post as a guest, your comment is unpublished.
    John · 4 years ago
    Very Helpful! how would you add a input box to change the comma separator?
  • To post as a guest, your comment is unpublished.
    Kap2 · 4 years ago
    Excellent - just what I was hoping to find. Made one of my job much easier. I believe it will handle up to 48 rows at a time so I had to 'chunk' my 350 lines down. Can you say why it will not take more than 48?

    Thanks again!
  • To post as a guest, your comment is unpublished.
    KiLian · 4 years ago
    Thanks, now i can easy convert column list to comma separated list with no space
  • To post as a guest, your comment is unpublished.
    Nichole · 4 years ago
    What causes the output to be this {4.6602853777055E+199} instead of {466028,537770,550024,etc}. It doesn't happen all of the time but it's happening now and driving me nuts. Any suggestions / fixes?
    • To post as a guest, your comment is unpublished.
      Jon654654684658 · 4 years ago
      # Nichole 2015-02-02 20:12
      What causes the output to be this {4.660285377705 5E+199} instead of {466028,537770, 550024,etc}. It doesn't happen all of the time but it's happening now and driving me nuts. Any suggestions / fixes?

      Hi Nichole,

      Looks like you have concatanated without a delimiter - i.e. missed out an &"," in whichever method you are using.

      Jon
      • To post as a guest, your comment is unpublished.
        Axel · 3 months ago
        I have the same problem. Even though I use the same VBL script for all numerical series, the output is sometimes as described above. Since I use the same script in all cases, I do not think that I missed out something.
  • To post as a guest, your comment is unpublished.
    sonu · 4 years ago
    Thanks for sharing the code, it was very useful.
    THANKS...!!!!
  • To post as a guest, your comment is unpublished.
    Roshni · 4 years ago
    This is what I am looking for. Thank you so much. Great work. :)
  • To post as a guest, your comment is unpublished.
    Robmez · 4 years ago
    Exactly what i was looking for.
    Thanks a lot for this elegant VBA-code.
  • To post as a guest, your comment is unpublished.
    Logesh · 4 years ago
    Thanks for the wonderful information. It worked :)
  • To post as a guest, your comment is unpublished.
    Nichole · 5 years ago
    Concatenate works perfectly fine for a small lists but this VBA code worked a charm and saved me tons of time. =) Thank you!
  • To post as a guest, your comment is unpublished.
    Josin · 5 years ago
    The above trick is very useful. Very good job