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

or

如何根据Excel中的条件轻松地连接文本?

假设我有一列ID号码,其中包含一些重复项和一列名称,现在,我想根据唯一ID号连接名称(如左侧截图所示),以便根据条件快速组合文本,我们如何才能在Excel中做什么?

doc根据标准1合并文本

根据用户定义函数的标准连接文本

根据Kutools for Excel的标准连接文本


基于另一列中的相同值连接文本:

您订购的 Kutools for Excel先进的组合行 utiltiy,你可以快速的将多个重复的行组合成一个基于关键列的记录,也可以对其他列应用一些计算,如sum,average,count等。

  • 1。 指定您想要组合其他列的键列;
  • 2。 为您的组合数据选择分隔符。

doc根据标准10合并文本

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!



要将文本与唯一ID号码结合使用,您可以先提取唯一值,然后创建一个用户定义的功能,以根据唯一ID组合名称。

1。 以下面的数据为例,首先需要提取唯一的ID号码,请应用这个数组公式: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""),将该公式输入到空白单元格中,例如D2,然后按 Ctrl + Shift + Enter 键在一起,看截图:

doc根据标准2合并文本

小技巧:在上面的公式中, A2:A15 是要从中提取唯一值的列表数据范围, D1 是要提取提取结果的列的第一个单元格。

2。 然后向下拖动填充手柄以提取所有唯一值直到显示空白为止,请参阅屏幕截图:

doc根据标准3合并文本

3。 在这一步中,你应该创建一个 用户自定义函数 要根据唯一的ID号码组合名称,请按住 ALT + F11 键,然后打开 Microsoft Visual Basic for Applications 窗口。

4。 点击 插页 > 模块,并将以下代码粘贴到 模块 窗口。

VBA代码:根据条件连接文本

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Update 20150414
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. 然后保存并关闭此代码,返回到工作表,然后将此公式输入到E2单元格中, = CONCATENATEIF($ A $ 2:$ A $ 15,D2,$ B $ 2:$ B $ 15,“,”) ,看截图:

doc根据标准4合并文本

6。 然后将填充手柄向下拖动到您要应用此公式的单元格,并根据ID号码组合所有相应的名称,请参阅截图:

doc根据标准5合并文本

提示:

1。 在上面的公式中, A2:A15 是您想要基于的原始数据, D2 是你提取的独特价值,而且 B2:B15 是您想要组合在一起的名称列。

2。 正如你所看到的,我组合了用逗号分隔的值,可以根据需要通过更改公式的逗号“,”来使用任何其他字符。


如果你有 Kutools for Excel,其 先进的组合行 实用程序,您可以快速方便地连接基于条件的文本。

Kutools for Excel : 与超过300方便的Excel加载项,在60天免费试用没有限制.

安装后 Kutools for Excel,请按以下步骤操作:

1。 根据一列选择要合并的数据范围。

2。 点击 Kutools > 内容 > 先进的组合行,看截图:

doc根据标准6 6合并文本

3。 在 基于列组合行 对话框中,单击ID列,然后单击 首要的关键 要将此列作为您的组合数据基础的关键列,请参阅截图:

doc根据标准7合并文本

4。 然后点击 名字 要合并这些值的列,然后单击 结合 选项,并为组合数据选择一个分隔符,请参阅屏幕截图:

doc根据标准8合并文本

5。 完成这些设置后,点击 OK 退出对话框,B列中的数据根据​​键列A组合在一起。请参见屏幕截图:

doc根据标准9合并文本

要了解关于此“组合行基于列”功能的更多详细信息.

有了这个功能,以下问题将尽快解决:

如何在Excel中将多行合并为一个并求和重复项?

立即下载并免费试用Kutools for Excel!


Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在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.
    AS · 28 days ago
    Hi,

    Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


    Thank you in advance. :)
  • To post as a guest, your comment is unpublished.
    Pete · 6 months ago
    Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
  • To post as a guest, your comment is unpublished.
    Merijn · 6 months ago
    BTW i used the VBA solution
  • To post as a guest, your comment is unpublished.
    Merijn · 6 months ago
    Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
    I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
  • To post as a guest, your comment is unpublished.
    Chantelle · 7 months ago
    How can I ignore blank cells? mine currently displays this:

    ";2503201111@msg.telus.com;;2503202222@msg.telus.com;2508193333@msg.telus.com;2503714444@msg.telus.com;;;;"

    I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
    • To post as a guest, your comment is unpublished.
      skyyang · 7 months ago
      Hello, Chantelle
      When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

      Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
      Dim xResult As String
      On Error Resume Next
      If CriteriaRange.Count <> ConcatenateRange.Count Then
      ConcatenateIf = CVErr(xlErrRef)
      Exit Function
      End If
      For i = 1 To CriteriaRange.Count
      If CriteriaRange.Cells(i).Value = Condition Then
      If ConcatenateRange.Cells(i).Value <> "" Then
      xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
      End If
      End If
      Next i
      If xResult <> "" Then
      xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
      End If
      ConcatenateIf = xResult
      Exit Function
      End Function

      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    victor · 9 months ago
    thank you very much! This was so simple and helped a lot!!
  • To post as a guest, your comment is unpublished.
    David · 1 years ago
    Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, David,

      To combine the cells with line break, the following User Defined Function may help you.

      Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
      Dim xResult As String
      On Error Resume Next
      If CriteriaRange.Count <> ConcatenateRange.Count Then
      ConcatenateIf = CVErr(xlErrRef)
      Exit Function
      End If
      For I = 1 To CriteriaRange.Count
      If CriteriaRange.Cells(I).Value = Condition Then
      xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
      End If
      Next I
      If xResult <> "" Then
      xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
      End If
      ConcatenateIf_LineBreak = xResult
      Exit Function
      End Function

      After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

      After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
  • To post as a guest, your comment is unpublished.
    Ahmed · 1 years ago
    So Easy, thank you :)
  • To post as a guest, your comment is unpublished.
    tien minh · 1 years ago
    Hi guys , I got an error #NAME? when I apply formulas CONCATENATEIF in excel file after set VBA code for this, could anyone help me to solve it , thanks som uch
  • To post as a guest, your comment is unpublished.
    krawlis · 2 years ago
    Is there a way to apply this CONCATENATEIF function in a separate sheet? It works when I put it in the same sheet as input data, but i need both tables in different sheets and it doesn't work.
    • To post as a guest, your comment is unpublished.
      Al Boulley · 1 years ago
      Yes, what you want to do is add the function to a module. Go into the VBA editor, right-click on "VBAProject" in the Project Explorer, mouse over the "Insert" menu item, and in that submenu choose "Module". Any functions you put in there will be useable on any sheet in your workbook.
  • To post as a guest, your comment is unpublished.
    MIchele · 2 years ago
    Is there a way to do this on Mac????
    It's exactly what I need - please let me know (or if any mac software would do it that you know of). Thx
  • To post as a guest, your comment is unpublished.
    DJDave · 2 years ago
    I had a problem after pasting this code into Excel 2016 - it contains non-regular spaces (perhaps non-breaking spaces?) which throw up syntax errors which are not evident no matter how closely you look because they are invisible! It is the indentation spaces that are the problem. Paste the code into Word and turn on hidden characters to see them.
    • To post as a guest, your comment is unpublished.
      Yash · 2 years ago
      Wow!! Genius! Worked like a charm! There ARE come spaces that show as a different character. Thanks a lot Dave! Wonder how you came up with the idea! Also, wonder how it works for some other peeps..Anyway, thanks again!
  • To post as a guest, your comment is unpublished.
    Yash · 2 years ago
    Hi!

    concactenateif is Exactly what I was looking for. But unfortunately can´t get it to work Always get a compile error:syntax error. Any ideas?

    In the past, with some imported VBA modules, I have noticed that I had to replace the "," by ";" as in my PC, maybe owing to my regional settings, that's the only way it works. Avidly use the built in sumifs etc. But can´t understand where am going wrong on this one.

    One more possibility that comes to mind is the fact that in office 365, "concat" replaces "concactenate". Can you help out please?

    Thanks in advance,

    Yash
    • To post as a guest, your comment is unpublished.
      DJDave · 2 years ago
      The code uses some non-breaking spaces for indentation, these trip up Excel2016. Hard to spot an invisible error..
  • To post as a guest, your comment is unpublished.
    Ram Bahadur Ale · 2 years ago
    It does not work for the big data range. I found that its working datarange is up to A2:A362. We would be grateful if you share the solution to cover the wider data range like A2:A200000 .....
    Thank you
  • To post as a guest, your comment is unpublished.
    Ram Bahadur Ale · 2 years ago
    It does not work for the big data range. I found it's working range is only up to A2:A362. We would be grateful if you share the solution for the big data range like A2:A200000 ....

    Thank you
    • To post as a guest, your comment is unpublished.
      Chris · 2 years ago
      Works great just slow. I am doing it with 27k lines of text in excel just set it off go for a brew and leave it to run
  • To post as a guest, your comment is unpublished.
    nickado · 2 years ago
    Great!!! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Matt · 2 years ago
    Awesome, thank you! I used the VBA solution and it worked great.
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 3 years ago
    Extremely helpful and nicely explained
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 3 years ago
    Explained in detailed and easy to understand, really helped when i was stuck at exact same situation.
  • To post as a guest, your comment is unpublished.
    latha · 3 years ago
    Taking more time for updating the same concatenateif() formula. i have 5000 rows. and its more than 2 hrs now its still updating :(

    Any resolution to make it work fast?
  • To post as a guest, your comment is unpublished.
    Renee · 3 years ago
    I am looking for a way to use a variation of this code to create a variant list based on master variant. Using your example data, I would need to combine columns A and B into unique identifiers and then concatenate those identifiers to each row based on the value in column A, excluding the value from from the combined for that row, and the rest in alpha sort order:

    Master id name id variant list
    CN20150012 Lucy CN20150012-Lucy CN20150012-Andy CN20150012-Monica CN20150012-Phiby
    US20150011 Tommas US20150011-Tommas US20150011-Rose
    CN20150012 Monica CN20150012-Monica CN20150012-Andy CN20150012-Lucy CN20150012-Phiby
    CN20150012 Phiby CN20150012-Phiby CN20150012-Andy CN20150012-Lucy CN20150012-Monica
    US20150011 Rose US20150011-Rose US20150011-Tommas
    UK20150014 Peter UK20150014-Peter UK20150014-Anith UK20150014-Kristi UK20150014-Libin
    JP20150010 Ramon JP20150010-Ramon JP20150010-Brenda JP20150010-James
    UK20150014 Libin UK20150014-Libin UK20150014-Anith UK20150014-Kristi UK20150014-Peter
    UK20150014 Anith UK20150014-Anith UK20150014-Kristi UK20150014-Libin UK20150014-Peter
    JP20150010 James JP20150010-James JP20150010-Brenda JP20150010-James JP20150010-Matus
    CN20150012 Andy CN20150012-Andy CN20150012-Lucy CN20150012-Monica CN20150012-Phiby
    UK20150014 Matus UK20150014-Matus JP20150010-Brenda JP20150010-James
    UK20150014 Kristi UK20150014-Kristi UK20150014-Anith UK20150014-Libin UK20150014-Peter
    JP20150010 Brenda JP20150010-Brenda JP20150010-James JP20150010-Ramon

    I have a sheet with over 1000 lines, each item comes with up to 4 variants. Trying to do this manually is impossible but I cannot find a solution that fits my needs.
  • To post as a guest, your comment is unpublished.
    Tim Blosser · 3 years ago
    This VBA code saved the day for me. Thank you!
  • To post as a guest, your comment is unpublished.
    Manoj · 3 years ago
    Will this tool be able to handle case sensitive combinations such as

    jABC 123
    abc 345
    ABc 678
    ABC 912
  • To post as a guest, your comment is unpublished.
    Enrique · 3 years ago
    Thanks for this code. It was EXACTLY what I needed. You saved me a lot of effort, thank you so much.
    • To post as a guest, your comment is unpublished.
      ConfusedNBusy · 2 years ago
      Thanks for posting this is exactly what I am looking for. I seem not to be saving the vba code correctly. I am getting an error message about ambiguous name found.

      Any suggestions or step by step on the VBA step of this project?

      Thanks
  • To post as a guest, your comment is unpublished.
    Kaladhar · 3 years ago
    This is an excellent solution (VBA code) and it addressed my requirements in minutes. I will refer your site to others and I will visit for everything that I need going forward.