Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelの基準に基づいてテキストを簡単に連結するにはどうすればよいですか?

いくつかの重複を含むID番号の列と名前の列があり、次に、左のスクリーンショットに示すように一意のID番号に基づいて名前を連結し、基準に基づいてテキストをすばやく結合したいとします。 Excelで行いますか?

docは、基準1に基づいてテキストを結合します

ユーザー定義関数を使用して、基準に基づいてテキストを連結します

基準に基づいてテキストをKutoolsforExcelで連結します


テキストを一意の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に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュール 窓。

VBAコード:基準に基づいてテキストを連結します

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
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アドインがあり、30日以内に制限なしで無料で試すことができます.

インストールした後 Kutools for Excel、次の手順で実行します。

1。 XNUMXつの列に基づいて、結合するデータ範囲を選択します。

2に設定します。 OK をクリックします。 クツール > マージ&スプリット > 高度な結合行、スクリーンショットを参照してください:

3。 の中に 列に基づいて行を組み合わせる ダイアログボックスで、[ID]列をクリックし、[ 主キー この列を、結合されたデータの基になるキー列にするには、スクリーンショットを参照してください。

docは、基準7に基づいてテキストを結合します

4。 そして、 お名前 値を結合する列をクリックし、 組み合わせる オプションを選択し、結合されたデータに対してXNUMXつのセパレーターを選択します。スクリーンショットを参照してください。

docは、基準8に基づいてテキストを結合します

5。 これらの設定が完了したら、をクリックします OK ダイアログを終了するには、列Bのデータがキー列Aに基づいて結合されています。スクリーンショットを参照してください。

docは、基準9に基づいてテキストを結合します

この機能を使用すると、次の問題ができるだけ早く解決されます。

複数の行をXNUMXつに結合し、Excelで重複を合計する方法は?

今すぐExcel用のKutoolsをダウンロードして無料トライアル!


Kutools for Excel:300以上の便利なExcelアドインがあり、30日以内に制限なしで無料で試すことができます。 今すぐダウンロードして無料トライアル!

最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性が50%向上し、毎日何百ものマウスクリックが減ります。
officetab下部
Say something here...
symbols left.
You are guest
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.
    Md. Zaker Hossain · 1 months ago
    @skyyang It worked like a charm sir. Thank you so much.
  • To post as a guest, your comment is unpublished.
    skyyang · 1 months ago
    @Md. Zaker Hossain Hi, Hossain,
    May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".
    =SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
    Please try, thank you!
  • To post as a guest, your comment is unpublished.
    Md. Zaker Hossain · 1 months ago
    Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
  • To post as a guest, your comment is unpublished.
    AA · 4 months ago
    Great function, exactly what I needed! Works like a charm
  • To post as a guest, your comment is unpublished.
    AS · 1 years 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 · 2 years 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 · 2 years ago
    BTW i used the VBA solution
  • To post as a guest, your comment is unpublished.
    Merijn · 2 years 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.
    skyyang · 2 years ago
    @cadrose97 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.
    cadrose97 · 2 years 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.
    victor · 2 years ago
    thank you very much! This was so simple and helped a lot!!
  • To post as a guest, your comment is unpublished.
    skyyang · 2 years ago
    @David 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.
    David · 2 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.
    Ahmed · 2 years ago
    So Easy, thank you :)
  • To post as a guest, your comment is unpublished.
    minhtien1900@gmail.com · 3 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.
    al.boulley@gmail.com · 3 years ago
    @krawlis 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.
    krawlis · 3 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.
    MIchele · 3 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.
    Yash · 3 years ago
    @DJDave 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.
    DJDave · 3 years ago
    @Yash 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.
    DJDave · 3 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.
    Chris · 3 years ago
    @Ram Bahadur Ale 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.
    Yash · 4 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.
    Ram Bahadur Ale · 4 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 · 4 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.
    ConfusedNBusy · 4 years ago
    @Enrique 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.
    nickado · 4 years ago
    Great!!! Thank you so much!
  • To post as a guest, your comment is unpublished.
    Matt · 4 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 · 4 years ago
    Extremely helpful and nicely explained
  • To post as a guest, your comment is unpublished.
    Samrat Govekar · 4 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 · 5 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 · 5 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 · 5 years ago
    This VBA code saved the day for me. Thank you!
  • To post as a guest, your comment is unpublished.
    Manoj · 5 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 · 5 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.
    Kaladhar · 5 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.