How to Combine Text Strings in Excel with Custom Delimiters

Manually merging scattered text strings in Excel is a tedious, error-prone struggle for busy data analysts. While compiling reports on standard funding sources-such as federal grants, state allocations, and private donations-users often face highly fragmented data. Fortunately, mastering the TEXTJOIN function grants users the power to aggregate disparate strings with custom delimiters instantly, saving hours of manual formatting. As a key stipulation, this specific formula requires Excel 2019 or Microsoft 365 to function. Below, we will explore concrete examples of this formula in action and outline a step-by-step guide to configuring your own custom delimiters.

Excel is widely recognized as a powerhouse for numerical analysis, but data professionals frequently find themselves wrestling with text-based data. One of the most common challenges is aggregating multiple text strings from a column or range into a single cell, separated by a custom; as a comma, semicolon, space, or line break).

Historically, performing this task in Excel was notoriously tedious, requiring cumbersome formulas or complex VBA macros. Fortunately, with modern Excel updates, Microsoft introduced powerful native functions that make text aggregation seamless. In this guide, we will explore how to aggregate text strings with custom delimiters using various methods, ranging from modern dynamic array formulas to legacy workarounds for older Excel versions.

The Modern Standard: The TEXTJOIN Function

If you are using Excel 2019, Excel 2021, or Microsoft 365, the absolute best tool for aggregating text is the TEXTJOIN function. Unlike its predecessor CONCATENATE, TEXTJOIN allows you to specify a; choose whether to ignore empty cells in your target range.

Syntax of TEXTJOIN

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: A text string (enclosed in quotation marks) that you want to insert between each text value. For example, ", " for a comma; a space, or CHAR(10) for a line break.
• ignore_empty: A boolean value (TRUE or FALSE). If set to TRUE, Excel will skip empty cells in the range, preventing consecutive delimiters.
• text1, [text2], ...: The text strings, individual cells, or ranges of cells that you want to join together.

Basic Example of TEXTJOIN

Imagine you have a list of employee names in cells A2:A6 (Alice, Bob, Charlie, David, Eve); you want to combine them into a single comma-separated string for an email distribution list. The formula is remarkably simple:

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

Result: Alice, Bob, Charlie, David, Eve

If cell A4 (Charlie) was blank, setting the second argument to TRUE ensures that Excel outputs Alice, Bob, David, Eve without leaving an awkward double comma (Alice, Bob, , David, Eve).

Advanced Scenario: Conditional Text Aggregation (TEXTJOIN + FILTER)

A common database operation is grouping records; concatenating their values-equivalent to the SQL GROUP_CONCAT or STRING_AGG functions. In Excel, you can achieve this by combining TEXTJOIN with the FILTER function (available in Microsoft 365).

The Scenario

Suppose you have a dataset containing projects; the employees assigned to them:

If you want to aggregate all employees working on Project Alpha into a single cell, separated by a semicolon, you can use the following formula:

=TEXTJOIN("; ", TRUE, FILTER(B2:B6, A2:A6 = "Alpha"))

How It Works

1. The FILTER(B2:B6, A2:A6 = "Alpha") array formula looks at column A, identifies which rows contain "Alpha",; returns an array of the corresponding names from column B: {"Alice"; "Charlie"; "Eve"}.
2. The TEXTJOIN function takes this array, ignores any blank spaces,; glues the names together using "; " as the custom delimiter.
3. Result: Alice; Charlie; Eve

Conditional Text Aggregation in Excel 2019 (TEXTJOIN + IF)

If you are using Excel 2019, you will not have access to the dynamic FILTER function. However, you can still achieve conditional aggregation by nesting an IF statement inside TEXTJOIN.

To run the same query as above, use this formula:

=TEXTJOIN("; ", TRUE, IF(A2:A6 = "Alpha", B2:B6, ""))

Note: If you are not on Microsoft 365, you must enter this as a legacy array formula by pressing Ctrl + Shift + Enter instead of just Enter. This wraps your formula in curly braces { }.

Legacy Solutions (For Excel 2016; Older)

If your organization is running Excel 2016 or older, TEXTJOIN is not natively available. In these environments, you have to resort to alternative workarounds.

Method 1: The Ampers; (&) Operator

For a small, fixed number of cells, you can manually construct the string using the concatenation operator (&):

=A2 & ", " & A3 & ", " & A4 & ", " & A5

Limitation: This method is highly impractical for large ranges. If you have 50 cells to aggregate, you would have to write out 50 references manually. Additionally, it does not h; le empty cells gracefully; you will end up with trailing or consecutive delimiters unless you write complex IF logic for every single cell.

Method 2: Creating a User-Defined Function (VBA)

To bypass the limitations of older Excel versions, you can create your own custom VBA function that mimics the behavior of TEXTJOIN. This is the cleanest workaround for legacy users.

To implement this, follow these steps:

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.
2. Click Insert > Module from the top menu.
3. Paste the following code into the empty module window:

Function CustomTextJoin(;
String, ignore_empty;
Boolean, rng;
Range);
String
    Dim cell;
Range
    Dim result;
String
    
    For Each cell In rng
        If Not (ignore_empty And Trim(cell.Value) = "") Then
            result = result & cell.Value & delimiter
        End If
    Next cell
    
    ' Remove the trailing delimiter
    If Len(result) > 0 Then
        result = Left(result, Len(result) - Len(delimiter))
    End If
    
    CustomTextJoin = result
End Function

Close the VBA editor. You can now use your custom function directly in your worksheet just like any other Excel formula:

=CustomTextJoin(", ", TRUE, A2:A6)

Advanced Formatting: Aggregating with Line Breaks

Sometimes, a comma or a semicolon isn't enough; you may want to format your aggregated text vertically, with each string on a new line inside the same cell. You can do this by using the CHAR function to generate a line break character.

• On Windows: Use CHAR(10)
• On Mac: Use CHAR(13)

Here is how you would write the formula for Windows users:

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

Crucial Step: To see the line breaks work correctly, you must select the destination cell and click the Wrap Text button on the Home tab of the Excel Ribbon. If Wrap Text is disabled, Excel will display the text on a single line with a tiny space or a strange character representing the line break.

Summary of Methods

To help you choose the right approach for your specific Excel environment, here is a quick summary table:

Conclusion

Aggregating text strings with custom delimiters used to be one of Excel's primary weaknesses. Today, thanks to the introduction of the TEXTJOIN function and dynamic arrays, it has become one of its strengths. Whether you are running the latest version of Microsoft 365 and utilizing the synergy of TEXTJOIN and FILTER, or deploying a reliable VBA workaround on a legacy machine, you now have the tools to cleanly merge, format, and structure text data with absolute precision.