How to easily concatenate text based on criteria in Excel?

Concatenating text based on specific criteria in Excel is a valuable technique that helps streamline data analysis, especially when handling large or complex datasets. This operation enables users to merge cell values according to a condition or key, making reports more readable and facilitating deeper insight into grouped data. For instance, suppose you want to list all names associated with each country; concatenating text by criteria can accomplish this succinctly. Below, you will find several practical methods to achieve this in Excel, suitable for different versions and user preferences.

• Concatenate text based on criteria with TEXTJOIN function (Excel365,2019 and later)
• Concatenate text based on criteria with Kutools for Excel(All versions)
• Concatenate text based on criteria with FILTER and TEXTJOIN (alternative for365/2021+)
• Concatenate text based on criteria with User Defined Function (All versions)

Concatenate text based on criteria with TEXTJOIN function

The TEXTJOIN function, used in combination with the IF function, offers an efficient solution to concatenate text based on a given condition in Excel. This method is available in Excel 2019, Excel 2021, and Excel 365. It's particularly useful when you want to merge values where a certain criterion is met, such as grouping all names belonging to a specific country. The approach is dynamic and adapts easily to changes in your data.

One advantage of this technique is its speed and formula-based flexibility, making it perfect for real-time data updates or large datasets. However, it should be noted that it is only available in newer versions of Excel. If you are using an older version, consider exploring the alternatives below.

1. Enter the following formula into the target cell (for example, E2):

=TEXTJOIN(", ", TRUE, IF($A$2:$A$15=D2, $B$2:$B$15, ""))

2. In Excel 2019, after entering the formula, use the key combination Ctrl + Shift + Enter to apply it as an array formula. In Excel 365/2021 and later, simply press Enter. Then, drag the fill handle down to fill the formula for other cells that represent different criteria. See screenshot below:

Tip: If you are working with very large datasets, enabling manual calculation mode or limiting the calculation area can help maintain performance. Double-check your ranges so they fully align; otherwise, the formula may return errors or unexpected results.

If your Excel does not support TEXTJOIN, or you need more flexibility (such as ignoring errors, using different delimiters, or handling blanks), continue reading for more universal methods.

Concatenate text based on criteria with Kutools for Excel

For users whose Excel version does not provide the "TEXTJOIN" function or those who prefer an interface-driven approach over formulas, Kutools for Excel delivers a straightforward solution for concatenating text based on criteria. This method eliminates the need for complex formulas and suits all versions of Excel, making it accessible to a wide user base. Kutools integrates seamlessly with Excel and is ideal for users who favor reliability and time savings in their daily workflow.

To use this feature, install Kutools for Excel first. Once installed, do the following:

1. Open your worksheet, then go to the "Kutools" tab, select "Super Lookup", followed by "One-to-many Lookup (returns multiple results)".
2. In the dialog that appears, designate the "Output range" where results will display, and the "Lookup values" which are your unique keys or criteria.
3. Select the complete dataset as your source table.
4. Set the "Key Column" (the column containing your criterion, e.g., country) and the "Return Column" (the column containing values to concatenate, e.g., names) using the provided dropdowns.
5. Click "OK" to execute the operation.
   

Outcome: Kutools will instantly concatenate the relevant text based on your specified criteria, displaying the results in the output range as shown below:

Advantages of using Kutools:

• Applicable to any version of Excel
• No need for formula input or array operations
• Reduces error risk from complex cell references
• Efficient for users unfamiliar with advanced formulas

Concatenate text based on criteria with FILTER and TEXTJOIN functions (Excel365/2021 or later alternative)

In Excel 365 or Excel 2021 and above, the combination of FILTER and TEXTJOIN provides another efficient and dynamic way to concatenate text values based on a condition. This is especially handy for users who wish to avoid array formulas but still leverage the latest Excel capabilities.

1. In the result cell (for instance, E2), input the formula below:

=TEXTJOIN(", ", TRUE, FILTER(B$2:B$15, A$2:A$15=D2))

2. Press Enter to confirm the formula, then use the fill handle to drag the formula down for as many rows as you need.

This method avoids the need for Ctrl + Shift + Enter and automatically adapts if data changes, making it highly robust for dynamic tables. In the formula above, B$2:B$15 is the column to concatenate, A$2:A$15 is the criterion reference, and D2 is the specific criterion value. Replace the ranges as needed for your dataset. Note that FILTER requires a compatible version of Excel.

Concatenate text based on criteria with User Defined Function

For users seeking a custom, flexible approach—especially in versions of Excel that lack dynamic array functions—a User Defined Function (UDF) written in VBA offers a robust solution for concatenating text based on given criteria. This method lets you tailor the separator or logic as needed and is highly practical in legacy Excel environments or for specific custom workflows. However, it requires basic familiarity with the Excel VBA editor and macro security settings.

1. Press the ALT + F11 keys to launch the "Microsoft Visual Basic for Applications" window.

2. In the VBA window, click "Insert" > "Module". Next, copy and paste the following code into the module panel that appears:

VBA code: concatenate text based on criteria

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

3. After pasting the code, save and close the code window. Return to your Excel worksheet. To use this UDF, enter a formula like the following into cell E2, then use the fill handle to drag the formula down for other values:

=CONCATENATEIF($A$2:$A$15, D2, $B$2:$B$15, ",")

Note: In the formula above, A2:A15 indicates the range with criteria (such as country), D2 is the criterion to match, and B2:B15 is the range of text to concatenate. The separator defaults to a comma (",") but can be changed to any character or string you prefer. Ensure that your criteria and concatenate ranges are the same size, otherwise the function will return an error.

Tips for UDFs:

• Macros must be enabled for UDFs to work.
• UDFs do not recalculate automatically when source data changes—press F9 to refresh if needed.

This VBA-based approach is best for users comfortable with macros, or when native Excel functions are unavailable. The flexibility to modify the VBA code supports advanced concatenation logic or custom formats.

When selecting the most suitable method for concatenating text by criteria in Excel, consider your Excel version, the frequency of operation (one-time vs. recurring), and your comfort with formulas or add-ins. If you encounter formula errors, double-check that your ranges match and that all references point to intended rows or columns. For solutions involving VBA, ensure macros are enabled, and for Kutools, verify successful installation. If results are unexpected or blank, confirm that there is actually data matching the selected criteria. Combining these approaches in your daily workflow can greatly enhance productivity, ensure clean data summaries, and reduce manual editing. If you need help with other data processing tasks, visit our tutorial center for more Excel insights.