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

or

如何在Excel中使用空格,逗号或其他分隔符将多个单元格组合到单元格中?

将多个列或行中的单元格组合到一个单元格中时,组合数据可能不会分开。 但是如果你想用指定的标记分隔它们,比如空格,逗号,分号或其他标记,你怎么办? 本教程将为您介绍一些方法。


使用指定的分隔符将单元格的行,列或范围连接到一个单元格中

Kutools for Excel 支持强大的功能 - 结合 它可以帮助您将基于行,列或一系列单元格的所有数据合并到一个记录中,而不会丢失数据。 请参阅以下演示。 点击下载Kutools for Excel!


使用公式将单元格的行或列连接到一个包含空格,逗号或其他分隔符的单元格中

在Excel中,您可以使用以下公式将一行单元格或一列单元格组合到一个单元格中。


方法A:使用“&”运算符组合单元格

在Excel中,您可以使用“&”运算符组合不同的文本字符串或单元格值。

请输入以下公式以连接多列中的单元格:(将结果与空格分开,您可以根据需要将空格替换为其他分隔符。)

=A2&" "&B2&" "&C2

然后,将填充柄向下拖动到要应用此公式的单元格,并将来自不同列的数据合并到一个单元格中,请参见屏幕截图:

  • 提示:
  • 在上面的公式中,您可以将&运算符之间的空格替换为其他分隔符,例如 =A2&"-"&B2&"-"&C2
  • 如果需要组合多行的单元格值,则只需更改单元格引用,如下所示: =A2&" "&A3&" "&A4&" "&A5&" "&A6&" "&A7

方法B:使用Concatenate函数组合单元格

Excel中的Concatenate函数还用于将多个文本字符串或单元格值连接到一个单元格中。

请将以下公式输入或复制到空白单元格中:

=CONCATENATE(A2, "-", B2, "-", C2)

然后,将填充柄向下拖动到要应用此公式的单元格,您将得到以下结果:

  • 提示:
  • 在上面的公式中,您可以将“ - ”字符替换为其他分隔符,例如 =CONCATENATE(A2, ";", B2, ";", C2)
  • 如果需要组合多行的单元格值,则只需更改单元格引用,如下所示: =CONCATENATE(A2, "-", A3, "-", A4, "-", A5, "-", A6, "-", A7)

方法C:使用Textjoin函数组合单元格

如果您有Excel 365及更高版本,则有新功能 - Textjoin,此功能还可以帮助您将多个单元格组合到一个单元格中。

将以下公式输入或复制到空白单元格中:

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

然后,拖动填充柄以将此公式应用于其他单元格,请参见屏幕截图:

  • 提示建议:
  • 在上面的公式中,您可以将“,”字符替换为其他分隔符,例如 =TEXTJOIN("/",TRUE,A2:C2)
  • 要组合多行的单元格值,您只需更改单元格引用,如下所示: =TEXTJOIN(",",TRUE,A2:A7)
  • 这个TEXTJOIN还可以将一系列单元格组合成一个带有分隔符的单个单元格,如下所示: =TEXTJOIN(",",TRUE,A2:C7)

办公室标签图片

裁员赛季即将到来,仍然缓慢运作?
-- Office Tab 提高您的步伐,节省50%的工作时间!

  • 惊人! 多个文档的操作比单个文档更加轻松和方便;
  • 与其他Web浏览器相比,Office Tab的界面更加强大和美观;
  • 减少成千上万的繁琐鼠标点击,告别颈椎病和老鼠手;
  • 被90,000精英和300 +知名公司选中!
全功能,免费试用30天 了解更多 现在就下载!

用户定义函数将单元格的行或列连接到一个单元格,其中包含空格,逗号或其他分隔符

在Excel中,您还可以创建用户定义函数,以将行或列的单元格组合为具有空格或指定标记的单个单元格。

1。 保持 ALT + F11 键盘上的键打开一个 Microsoft Visual Basic for Application 窗口。

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

VBA:将基于行或列的单元格合并到具有特定分隔符的单元格中:

Function Combine(WorkRng As Range, Optional Sign As String = "~") As String
Dim Rng As Range
Dim OutStr As String
For Each Rng In WorkRng
    If Rng.Text <> " " Then
        OutStr = OutStr & Rng.Text & Sign
    End If
Next
Combine = Left(OutStr, Len(OutStr) - 1)
End Function

小技巧:在上面的脚本中“Function Combine(WorkRng As Range, Optional Sign As String = "~") As String“,分隔符”~“指定将组合结果分开,您可以更改它以满足您的需要。

3。 然后请输入公式 =Combine(A2:C2) 在空白单元格中,然后将填充手柄拖过要应用此公式的范围,则该行中的所有单元格都将合并到带有短划线的单元格中。 看截图:

提示建议:使用上面的用户定义函数,您还可以根据列组合单元格值,只需输入此公式即可 =Combine(A2:A7) 根据需要获取合并数据。


使用公式将单元格的行或列连接到一个具有换行符的单元格中

有时,您可能希望使用换行符来分隔连接的文本字符串,通常,CHAR(10)将返回换行符。 在这里,您可以使用以下方法来解决此任务:


方法A:使用连接函数将单元格与换行符组合在一起

在这里,您可以将连接函数与Char(10)字符组合在一起,以获得由换行符分隔的合并结果。

1。 请输入或复制以下公式:

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2)

然后,将填充柄向下拖动到要应用此公式的单元格,您将获得以下结果:

2。 然后,你应该点击 主页 > 包装文本 格式化单元格,然后,您将获得所需的结果:

提示建议:要组合多行的单元格值,您只需更改单元格引用,如下所示: =CONCATENATE(A2,CHAR(10),A3,CHAR(10),A4,CHAR(10),A5,CHAR(10),A6,CHAR(10),A7)


方法B:使用Textjoin函数将单元格与换行符组合在一起(Excel 365及更高版本)

如果需要组合多个单元格,可能上面的公式有点困难,因此,Textjoin函数可以快速,轻松地处理这个任务。

请将以下公式输入或复制到空白单元格中:

=TEXTJOIN(CHAR(10),TRUE,A2:C2)

获得合并结果后,请记住将公式单元格格式化为 包装文本,看截图:

提示建议:要组合多行的单元格值,您只需更改单元格引用,如下所示: =TEXTJOIN(CHAR(10),TRUE,A2:A7)


通过使用精彩功能将单元格的行,列或范围连接到具有指定分隔符的一个单元格中

如果您对上述公式和代码感到烦恼,我可以在这里推荐一个有用的工具 - Kutools for Excel,其 结合 功能,您可以按行,列或范围快速将单元格值组合到一个单元格中。

提示:申请这个 结合 功能,首先,你应该下载 Kutools for Excel,然后快速轻松地应用该功能。

安装后 Kutools for Excel,请这样做:

1。 选择要合并到单元格中的范围单元格值。

2。 请申请 结合 功能通过点击 Kutools > 结合。 看截图:

3。 在 组合列或行 对话框,请在下面的屏幕截图中指定您需要的操作:

4。 然后,点击 Ok,您将得到如下截图所示的结果:

1)。 将单元格值合并为每行的一个单元格:

2)。 将单元格值合并为每列的一个单元格:

3)。 将一系列单元格值组合到一个单元格中:

点击下载Kutools for Excel和免费试用版吧!


更多相对组合行和列文章:

  • 在Excel中合并并组合行而不丢失数据
  • 如果应用“合并和居中”命令(“对齐”面板上的“主页”选项卡>“合并和居中”)以在Excel中合并数据行,Excel仅将数据保留在左上角的单元格中。 用户必须使用另一种方法将多行数据合并为一行而不删除数据。
  • 组合重复行并在Excel中汇总值
  • 在Excel中,如果您有一系列包含重复条目的数据,并且现在要合并重复数据并在另一列中总结相应的值,则可能会遇到此问题,如以下屏幕截图所示。 你怎么能解决这个问题?
  • 连接单元格在Excel中忽略或跳过空白
  • Excel的Concatenate功能可以帮助您快速将多个单元格值组合到一个单元格中,如果所选单元格中有一些空白单元格,此功能也将组合空白。 但是,有时候,你只想用数据连接单元格并跳过空单元格,你怎么能在Excel中完成它?


  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 和保持数据; 分裂细胞含量; 结合重复行和总和/平均值...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 喜欢并快速插入公式,范围,图表和图片; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 数据透视表分组 周数,星期几等...... 显示未锁定,锁定的单元格 用不同的颜色; 突出显示具有公式/名称的单元格...
kte tab 201905
  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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.
    Lavina · 1 years ago
    I would like to know if I have a list of people with their information in different cells in one column but different row how to combine them in one cell For e,g I have one person teaching English, math and science and his name is repeated in one column but the courses are in different rows so I want to combine all the courses in one cell for that teacher. if I have 50 teachers I need to have all the courses that the teacher is teaching in one cell.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Lavina,
      Do you mean to combine multiple cell values in a column based on duplicate names in another column as below screenshot shown:

      You can insert a screenshot to make your problem more intuitive.
  • To post as a guest, your comment is unpublished.
    bin · 1 years ago
    VBA doesn't work for me, excel 2016.
  • To post as a guest, your comment is unpublished.
    Brian · 1 years ago
    Working on trying to get your VBA code to work however I keep getting a #NAME? error. How do I fix this? I've used this code before and it worked wonderfully but now it gives me an error.
  • To post as a guest, your comment is unpublished.
    Sagar · 2 years ago
    But it is not working for large data. I have around 50000 rows to combine in single cell.
  • To post as a guest, your comment is unpublished.
    susan · 2 years ago
    415xxe 5687sdc sa4567
    415xxe 5687sdc sa4567
    415xxe 5687sdc sa4567
    415xxe 5687sdc sa4567

    I need to separate these in different columns and then put a comma behind the last column
  • To post as a guest, your comment is unpublished.
    Joe · 2 years ago
    The VBA code does not work

    I get an error Microsoft Visual Basic for Applications - Compile error: Syntax error

    then the line "If Rng.Text "," Then" is highlighted in blue and on the top highlighted in yellow it says this "function combine(workrng as range, optional sign as string = ",") as string

    I am using excel 2016 on a pc
  • To post as a guest, your comment is unpublished.
    Smithc114 · 2 years ago
    I genuinely enjoy studying on this website, it holds good content. Never fight an inanimate object. by P. J. O'Rourke. dfdkbafbadfkagdd
  • To post as a guest, your comment is unpublished.
    Sandeep K · 2 years ago
    how to combine 2 cell with space in between with 2 independent cell format. Eg. If one cell is Red digits & other cell has digits in green, it should combine with Red & green digits.
  • To post as a guest, your comment is unpublished.
    Jake · 2 years ago
    The VBA code worked really well thanks.

    My only issue is that it is including blanks so that my combined output ends up looking like this: "test, test, , , , , , test"

    How could I get it to exclude blank cells within the range?
    • To post as a guest, your comment is unpublished.
      Kashish Garg · 2 years ago
      If you found the answer let me now please as well.
      • To post as a guest, your comment is unpublished.
        Carontoc · 2 years ago
        instead of the line in the original code:

        If Rng.Text <> ", " Then
        OutStr = OutStr & Rng.Text & Sign


        you need to add a "test" to determine if the cell is empty followed by the statement above which adds the delimiter. Delete the above 2 lines and then Copy the following code in and your COMBINE function will remove the blanks from your list.

        If IsEmpty(Rng.Value) = True Then

        OutStr = OutStr & ""

        ElseIf Rng.Text <> ", " Then

        OutStr = OutStr & Rng.Text & Sign
  • To post as a guest, your comment is unpublished.
    Charles · 2 years ago
    The VBA macro worked very well after I adjusted for the my cell locations and I was able to add a space after the comma for a better display of the data.
  • To post as a guest, your comment is unpublished.
    laura · 3 years ago
    the merge formula was great, except one of my columns of data were dates, and the dates had to be formatted MM/DD/YY - but once merged, the date converted to a decimal figure. I couldn't use the formula.
  • To post as a guest, your comment is unpublished.
    Mark · 3 years ago
    How do i get results show the following values:
    A B C
    10 10 =CONCATENATE(A1,",",B1,",",C1) RESULTS 10,,10
    10 20 30 =CONCATENATE(A1,",",B1,",",C1) RESULTS 10,20,10
    10 30 =CONCATENATE(A1,",",B1,",",C1) RESULTS 10,30,

    ABOVE IT IS SHOWING COMMA IN BETWEEN TWO VALUES OR AFTER. I DONT WANT THE COMMA WHERE THE VALUE IS NOT THERE
    • To post as a guest, your comment is unpublished.
      Rob · 2 years ago
      Try this.

      =SUBSTITUTE(IF(A1="","",A1&",") & IF(B1="","",B1) & IF(C1="","","," & C1),",,",",")
  • To post as a guest, your comment is unpublished.
    Sawan Sharma · 3 years ago
    Thanks a ton VBA save my hours....!!!
  • To post as a guest, your comment is unpublished.
    Oscar Bolanos · 3 years ago
    Thanks for the macro, it worked great!!
  • To post as a guest, your comment is unpublished.
    cara · 4 years ago
    I am having issues, the cells G2-N2 (down thousands of rows) need to be merged to one column using a comma but no space between them. However, if the cell is EMPTY, it needs to SKIP the cell, with no input of a comma. I cannot find any HELP menu for this and I am up against a deadline and need this shortcut for thousands of products I am importing. Any and all help appreciated.
  • To post as a guest, your comment is unpublished.
    Subbu · 4 years ago
    Thanks a lot.. this info is very helpful.
  • To post as a guest, your comment is unpublished.
    R Hammer · 4 years ago
    Thank you. That is the best explanation I have found yet! I am having trouble expanding this over hundreds of columns though. Can you clarify the point where you say "Dragging the fill handle over the range that you want to apply this formula, all of the cells in a row will be combined into a cell with commas" I am not sure how to do this.
  • To post as a guest, your comment is unpublished.
    sandeep melwan · 4 years ago
    i want to merage two cells with the following results
    A and B = A ,B
    means A column space comma B column
  • To post as a guest, your comment is unpublished.
    Pradeep · 4 years ago
    Thanks for the VBA code!!!!It saved my time......... :-)
  • To post as a guest, your comment is unpublished.
    Srina · 4 years ago
    Hi,
    thanks so much for the helpful resource!!!
    May I ask how to modify the VBA Module to have both "," and " "?
    Thank you so much!
    • To post as a guest, your comment is unpublished.
      Srina · 4 years ago
      [quote name="Srina"]Hi,
      thanks so much for the helpful resource!!!
      May I ask how to modify the VBA Module to have both "," and " "?
      Thank you so much![/quote]

      Most of all, how to end the list with no space and no comma? So to select entire rows of a spreadsheet as resource, which might include a different number of columns?

      Please help, as Kutools is not compatible with Mac.

      Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Annette · 5 years ago
    I used your VBA formula a few days ago and it worked beautifully. I'm using it again today and I keep getting a #NAME? error. I've tried all sorts of things and can't get it to work. What am I doing wrong?
  • To post as a guest, your comment is unpublished.
    admin_jay · 5 years ago
    Hello, please be more specific about your issue.

    Try to contact me at jaychivo#extendoffice.com. Please replace # with @.

    :-)
    • To post as a guest, your comment is unpublished.
      Kristi · 2 years ago
      Hi!

      I have a spreadsheet of over 23,000 serial numbers in Column A, then each serial number has a one-digit Assign number in Column B, and finally an individualized Application number that belongs to both the serial number and assigned number in Column C.

      Looks like this:

      A B C
      123456 1 1212121
      987655 2 5656565
      606060 1 4343434
      606060 1 8989898

      How would I combine just the rows of data that have the SAME Serial number and Assign number but different application numbers using a comma?

      Such as, I need:

      606060 1 4343434, 8989898

      Any help would be super appreciated!!!
  • To post as a guest, your comment is unpublished.
    Zygis · 5 years ago
    Thank you. This was really a savior! I have one question. I want to merge EAN codes. It works like a charm in the same sheet. But when I take EAN codes from another sheet, I get the result:

    #######,########,#######,#######

    Where might be the problem?

    many thanks
  • To post as a guest, your comment is unpublished.
    fmanjeim · 5 years ago
    Great, limpidly clear
  • To post as a guest, your comment is unpublished.
    Charuta · 5 years ago
    awesome: helped with my project and saved many hours of work...thanks much.
  • To post as a guest, your comment is unpublished.
    Raghad · 5 years ago
    Thank you a lot. I found this very useful.
    Regards,
    Raghad