Excel Formulas to Combine Text Values with Commas

Manually merging cell data in Excel often results in cluttered, unpunctuated text strings that are difficult to read. When consolidating critical spreadsheet data, such as standard funding sources like federal grants, private equity, and bank loans, maintaining clean presentation is essential. Implementing an automated delimiter formula grants stakeholders instant visual clarity and professional-grade reporting. Note this structural stipulation: older Excel versions require nested functions, whereas modern Excel leverages streamlined array logic. For example, combining "Federal Grant" and "Private Donation" requires precise comma insertion. Below, we detail the exact TEXTJOIN formulas to seamlessly automate your data formatting.

Combining text from multiple cells is one of the most common tasks in Excel. Whether you are merging first and last names, building a mailing address, compiling a list of product codes, or consolidating tags, you often need a clean separator-most commonly a comma-between these values.

While merging text sounds simple, the real challenge arises when you have empty cells in your range. A naive formula can quickly result in ugly formatting, leaving you with duplicate commas or trailing delimiters like "Apple, Orange, , Grape, ".

In this comprehensive guide, we will explore the best Excel formulas to combine text with commas, ranging from modern, effortless functions to highly robust workarounds for older Excel versions.

Method 1: The Modern Champion – The TEXTJOIN Function

If you are using Excel 365, Excel 2019, or Excel for the Web, your search ends here. The TEXTJOIN function is specifically designed to solve the problem of joining text with delimiters while gracefully handling empty cells.

The Syntax

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

• delimiter: The character(s) you want to place between each text value. To add a comma and a space, use ", " (wrapped in double quotes).
• ignore_empty: A boolean value. Set this to TRUE to skip empty cells entirely, preventing double commas. Set to FALSE if you want to include empty cells.
• text1, text2, ...: The cells, ranges, or text strings you want to combine.

Step-by-Step Example

Imagine you have a row of tags in cells A2 to D2: "Excel", [Empty], "Formulas", "Tutorial". To combine them with a comma and space, use this formula:

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

The Result: Excel, Formulas, Tutorial

Because the second argument is set to TRUE, Excel intelligently skips the blank cell in B2, producing a perfectly formatted string without extra commas.

Method 2: The Classic Ampersand Operator (&) for Older Excel Versions

If you are working on an older version of Excel (such as Excel 2010, 2013, or 2016) that does not support TEXTJOIN, you will need to rely on the ampersand (&) concatenation operator.

The Basic Approach

If you are 100% sure that none of your cells will ever be empty, you can simply chain them together with commas:

=A2 & ", " & B2 & ", " & C2 & ", " & D2

However, if cell B2 is blank, this formula returns: Excel, , Formulas, Tutorial. This looks highly unprofessional. To prevent this, we must introduce conditional logic.

The "Clean" Ampersand Solution (Using IF and MID)

To safely concatenate cells with commas in older Excel versions without getting double or trailing commas, you can use a clever trick involving the MID and IF functions. This formula prefixes each non-empty value with a comma, and then strips away the very first comma:

=MID(IF(A2<>"", ", " & A2, "") & IF(B2<>"", ", " & B2, "") & IF(C2<>"", ", " & C2, "") & IF(D2<>"", ", " & D2, ""), 3, 9999)

How This Works:

1. Each IF(cell<>"", ", " & cell, "") checks if a cell is not blank. If it has data, Excel adds a comma and space before the value (e.g., , Excel). If it is blank, it outputs nothing.
2. All these pieces are concatenated together. If cells A2, C2, and D2 have data, the intermediate output looks like this: , Excel, Formulas, Tutorial.
3. The MID(..., 3, 9999) function extracts the text starting from the 3rd character to the end. This effectively cuts off the initial comma and space (, ), leaving you with a perfectly clean string: Excel, Formulas, Tutorial.

Method 3: CONCAT and CONCATENATE (Use with Caution)

Excel has two native concatenation functions: CONCATENATE (older) and CONCAT (introduced in Excel 2016 to replace CONCATENATE).

While they can combine text, they are highly inefficient for adding separators because you must manually add the comma as an argument between every single cell:

=CONCAT(A2, ", ", B2, ", ", C2, ", ", D2)

Just like the basic ampersand operator, CONCAT does not have an built-in mechanism to ignore blank cells. Because of this, TEXTJOIN is always preferred over CONCAT when delimiters are involved.

Advanced Scenario 1: Combining an Entire Vertical Column into One Cell

What if your data is arranged vertically in a column (e.g., A2:A10) and you want to compile all these values into a single comma-separated cell?

With TEXTJOIN, this is incredibly easy. You just pass the vertical range as the third argument:

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

If you are using legacy Excel, you cannot easily do this with a standard formula without manually typing out all 9 cells. In this case, copying the column, pasting it into a text editor, or using a VBA User-Defined Function (UDF) is highly recommended (see the VBA section below).

Advanced Scenario 2: Conditional Text Combining (TEXTJOIN with IF)

Sometimes you only want to combine values that meet a specific condition. For example, suppose you have a table of employees, their departments, and you want to list all employees who belong to the "Sales" department in a single cell, separated by commas.

You can accomplish this by combining TEXTJOIN with an IF statement:

=TEXTJOIN(", ", TRUE, IF(B2:B10="Sales", A2:A10, ""))

Note: If you are using Excel 2019, you must press Ctrl + Shift + Enter to execute this as an array formula. In Excel 365, simply pressing Enter works perfectly thanks to dynamic arrays.

How It Works:

The IF function evaluates the range B2:B10. If a cell equals "Sales", it returns the corresponding name from A2:A10; otherwise, it returns an empty string (""). The TEXTJOIN function then takes these results, ignores the empty strings, and merges the Sales team names together seamlessly.

Legacy Excel Solution: Custom VBA TEXTJOIN Function

If your organization is stuck on Excel 2013 or 2016 and you frequently need to combine values with commas while ignoring blanks, you can write a simple macro to recreate the TEXTJOIN function.

Press ALT + F11 to open the VBA editor, click Insert > Module, and paste the following code:

Function MYTEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
    Dim cell As Range
    Dim result As String
    
    For Each cell In rng
        If Not (ignore_empty And Len(Trim(cell.Value)) = 0) Then
            result = result & cell.Value & delimiter
        End If
    Next cell
    
    If Len(result) > 0 Then
        MYTEXTJOIN = Left(result, Len(result) - Len(delimiter))
    Else
        MYTEXTJOIN = ""
    End If
End Function

Close the editor. You can now use this custom function in your spreadsheet just like a native Excel function:

=MYTEXTJOIN(", ", TRUE, A2:D2)

Summary: Which Formula Should You Use?

Conclusion

Formatting errors like double commas or dangling separators can ruin the look of an otherwise pristine Excel report. For modern Excel users, TEXTJOIN is the ultimate tool to cleanly combine values. If you are operating on an older spreadsheet environment, leveraging the MID and IF logic loop will ensure your combined text remains clean, professional, and completely free of formatting glitches.