Manually merging Excel columns with delimiters is tedious and error-prone, especially when managing complex datasets. When consolidating financial reports-such as listing standard funding sources like venture capital, federal grants, and private equity-traditional concatenation methods fall short. Fortunately, the TEXTJOIN function grants analysts the power to instantly unify ranges while ignoring blank cells. As a stipulation, this modern efficiency requires Excel 2019 or Microsoft 365. For example, combining "Grants, VC, Debt" becomes effortless. Below, we outline the exact formula syntax and step-by-step implementation to streamline your spreadsheet workflow.

Excel Formula to Concatenate Column Range with Delimiter

Concatenating a range of cells in a column while separating them with a specific character-such as a comma, space, semicolon, or line break-is one of the most common data manipulation tasks in Microsoft Excel. Whether you are preparing list data for SQL queries, merging email addresses for a mailing list, or building a clean, readable summary from a list of items, knowing the right formula can save you hours of manual work.

Historically, combining range values with delimiters in Excel was surprisingly tedious. However, with modern Excel functions and a few clever workarounds for older versions, you can easily achieve this. This comprehensive guide covers all the methods to concatenate a column range with delimiters, spanning modern functions, legacy compatibility formulas, Power Query, and VBA solutions.

Method 1: The Modern & Best Way – Using the TEXTJOIN Function

If you are using Excel 365, Excel 2019, or Excel for the Web, the absolute best tool for this job is the TEXTJOIN function. Unlike the older CONCATENATE function, TEXTJOIN allows you to specify a delimiter and choose whether to ignore empty cells in your range.

The Syntax:

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

Parameters:

• delimiter: The character or string you want to place between each cell value (wrapped in double quotes). For example, ", " (comma and space) or " - " (hyphen with spaces).
• ignore_empty: A boolean value. Use TRUE to skip blank cells in your column range, or FALSE to include them (which results in consecutive delimiters).
• text1, text2, ...: The range of cells you want to merge (e.g., A2:A10).

Example 1: Merging a Column with a Comma and Space

Suppose you have a list of names in column A (from cell A2 to A10) and you want to join them into a single cell separated by a comma and a space. You would use the following formula:

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

If cell A5 is empty, Excel will automatically skip it without creating an ugly, double-comma error (e.g., "John, Sarah, , David"), thanks to the TRUE argument.

Example 2: Concatenating with Line Breaks

If you want to arrange the values in a single cell but on separate lines, you can use the CHAR(10) function as your delimiter, which represents a line break in Excel:

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

Note: For the line breaks to display correctly in your destination cell, you must enable Wrap Text on that cell (Home tab > Alignment group > Wrap Text).

Method 2: For Excel 2016 – Using the CONCAT Function

Excel 2016 introduced the CONCAT function as a replacement for CONCATENATE. While CONCAT can accept a full column range (unlike its predecessor), it does not natively support delimiters. If you write =CONCAT(A2:A10), it will merge the cells seamlessly without any spaces or commas.

To bypass this limitation and insert a delimiter, you have to pair it with an array formula or use a helper column. However, a common workaround is to use CONCAT alongside a delimiter referencing array behavior in Excel 365/2019, though at that point, using TEXTJOIN is much easier.

If you must use CONCAT and want delimiters, you can structure it like this (requires entering with Ctrl + Shift + Enter in non-365 versions):

=CONCAT(A2:A10 & ", ")

Drawback: This method will leave a trailing delimiter at the very end of your merged string (e.g., "John, Sarah, David, "). You would need to strip the trailing characters using the LEFT and LEN functions.

Method 3: Legacy Compatibility – Using Ampersand (&) or CONCATENATE

If you are working on an older version of Excel (Excel 2013 or earlier) or sharing your worksheet with users who have outdated Excel builds, you cannot use TEXTJOIN or CONCAT. You must fall back on manual concatenation.

The Manual Stringing Method

You can merge cells manually using the & operator:

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

This method is straightforward but highly impractical for columns containing more than five or ten cells, as it requires typing every cell reference individually.

The Quick Helper-Column Shortcut

To avoid manually typing a massive formula, you can build a cumulative concatenation sequence down a helper column:

1. In cell B2, enter: =A2
2. In cell B3, enter: =B2 & ", " & A3
3. Drag this formula down to the bottom of your data range (e.g., B10).
4. The very last cell (B10) will contain the fully concatenated column range with your delimiters. You can copy this value and "Paste as Values" wherever you need it.

Method 4: Formatting Data within Concatenations (Using the TEXT Function)

When you concatenate values in Excel, numbers, dates, and currency values lose their formatting and revert to raw, unformatted data. For instance, a date like "2023-10-25" might turn into its raw serial number "45224".

To preserve formatting when combining a column range with a delimiter, nest the TEXT function inside your TEXTJOIN formula. Because this requires evaluating an array of cells, you must apply the formatting rule to the entire range:

=TEXTJOIN(", ", TRUE, TEXT(A2:A10, "mm/dd/yyyy"))

If you are concatenating currency, you can write:

=TEXTJOIN(" | ", TRUE, TEXT(A2:A10, "$#,##0.00"))

Note: If you are not using Excel 365, you may need to press Ctrl + Shift + Enter to execute this as an array formula.

Method 5: The Power Query Route (For Large Datasets)

If you are dealing with thousands of rows, complex data transformation, or regularly refreshed external data, Excel formulas can slow down your workbook. Power Query is the most efficient engine for this task.

Step-by-Step Power Query Method:

1. Select your data range, navigate to the Data tab, and click From Table/Range.
2. In the Power Query Editor window, go to the Transform tab.
3. Click on Group By.
4. In the Group By dialog:
   • Set "Operation" to Sum (temporarily, to generate the basic syntax).
   • Set "Column" to your target column.
5. Go to the Formula Bar and modify the generated code. Look for List.Sum in the M code and change it to:

   Text.Combine(List.Transform([YourColumnName], Text.From), ", ")

6. Click Close & Load to send your concatenated list back into an Excel sheet.

Method 6: Creating a Custom TEXTJOIN Equivalent with VBA

If you are using an older version of Excel but want the elegance of TEXTJOIN without writing long manual formulas or using helper columns, you can create a custom User Defined Function (UDF) using VBA.

The VBA Code:

Function CustomJoin(CellRange As Range, Delimiter As String) As String
    Dim Cell As Range
    Dim Result As String
    
    For Each Cell In CellRange
        If 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
    
    CustomJoin = Result
End Function

How to Implement This:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the code above into the module window.
4. Close the VBA Editor and return to your sheet.
5. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Now, you can use your custom function just like a native formula:

=CustomJoin(A2:A10, ", ")

Summary Comparison Table

By identifying your Excel version and data volume, you can select the perfect method to quickly and cleanly merge your column ranges with delimiters.