How to Combine Multiple Cells in Excel and Ignore Blank Values

Consolidating spreadsheet data often leads to frustration when empty cells leave behind awkward, lingering delimiters. This challenge frequently arises when compiling reports from standard funding sources, such as municipal budgets or private donations. Fortunately, Excel's TEXTJOIN function grants users the ability to automatically ignore these blank gaps, streamlining your data aggregation. The only stipulation is that this modern formula requires Excel 2019 or Microsoft 365. For example, combining "Federal Grants," an empty cell, and "Endowments" seamlessly results in "Federal Grants, Endowments." Below, we will break down the exact formula syntax to implement this solution efficiently.

When working with large datasets in Excel, one of the most common data-cleaning tasks is combining information from multiple columns into a single cell. Whether you are merging first and last names, stitching together mailing addresses, or compiling list items, consolidating text is a daily chore for many data analysts.

However, a major hurdle arises when some of your cells are empty. If you use a simple concatenation method, you often end up with awkward, messy results containing consecutive delimiters, such as double commas (e.g., "John, , Smith, Chicago") or trailing spaces. To create clean, professional reports, you must use formulas that smart-filter these blank cells.

In this comprehensive guide, we will explore the best Excel formulas to combine multiple cells while ignoring empty values, ranging from modern, elegant solutions to workarounds for older Excel versions.

Method 1: The Modern Champion – The TEXTJOIN Function

If you are using Excel 2016, Excel 2019, Excel 2021, or Microsoft 365, your absolute best option is the TEXTJOIN function. This powerful formula was specifically designed to solve the exact problem of combining ranges while ignoring empty values.

The TEXTJOIN Syntax

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

The arguments are straightforward:

• delimiter: The character(s) you want to place between each combined text value (e.g., a comma, space, hyphen, or line break). This must be enclosed in double quotes (e.g., ", ").
• ignore_empty: A boolean value. Setting this to TRUE tells Excel to completely ignore any empty cells in the specified ranges. Setting it to FALSE will include empty cells, resulting in consecutive delimiters.
• text1, text2, ...: The actual cells, ranges, or arrays of text you want to combine.

Step-by-Step Example

Imagine you have a table containing address parts across columns A, B, C, and D, and you want to combine them into Column E with a comma and a space as the separator.

To achieve this in cell E2, enter the following formula:

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

How it works: Excel looks at the range A2:D2. It identifies that B2 is empty. Because the second argument is set to TRUE, Excel skips B2 entirely and merges A2, C2, and D2 seamlessly, outputting "123 Pine St., Seattle, WA" without any extra commas.

Method 2: Combining Cells in Older Excel Versions (Excel 2013 and Earlier)

If you or your colleagues are running legacy versions of Excel, you won't have access to TEXTJOIN. Instead, you have to construct a formula using the classic CONCATENATE function (or the ampersand & operator) combined with logical IF statements to skip blank cells.

The "Ampersand and IF" Approach

To avoid ugly double delimiters, you must evaluate whether each cell is blank before appending the delimiter. Here is how you can write this logic using the address example above:

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

Understanding the Logic

Let's break down this nested logic so you can easily adapt it to your worksheets:

1. Start with the first cell: =A2. (Assuming your first cell will always have data. If it might be blank, you'll need to wrap that in an IF statement too).
2. Evaluate the second cell: IF(B2<>"", ", " & B2, "") checks if cell B2 is not empty (<>""). If it contains data, Excel appends a comma, a space, and the value of B2. If B2 is empty, it appends an empty string (""), adding nothing to the string.
3. Repeat for subsequent cells: The process is repeated for cell C2 and cell D2. Each step dynamically decides whether or not to insert the delimiter.

While this method is significantly longer and more tedious to write than TEXTJOIN, it is highly reliable and works on every version of Excel ever released.

Method 3: Handling Formula-Generated Blanks (Pseudo-Blanks)

Sometimes, a cell is not truly empty; instead, it contains a formula that returns an empty string (e.g., =IF(F2="","","Pending")). These are known as pseudo-blanks.

The good news is that both TEXTJOIN(..., TRUE, ...) and the IF(cell<>"", ...) construction recognize formula-returned empty strings ("") as empty values and will ignore them automatically. You do not need to make any structural modifications to your formulas to handle these cases.

Method 4: Utilizing VBA for a Custom "TEXTJOIN" in Legacy Excel

If you are stuck on an old version of Excel but have to combine massive ranges of cells, writing long IF statements can quickly become a nightmare. In these scenarios, you can write a short VBA User Defined Function (UDF) to recreate the functionality of TEXTJOIN.

The VBA Code

Open the VBA editor (press ALT + F11), insert a new module (Insert > Module), and paste the following code:

Function CompatibleTextJoin(Delimiter As String, IgnoreEmpty As Boolean, TargetRange As Range) As String
    Dim Cell As Range
    Dim Result As String
    
    For Each Cell In TargetRange
        If Not (IgnoreEmpty And Trim(Cell.Value) = "") Then
            If Cell.Value <> "" Then
                Result = Result & Cell.Value & Delimiter
            End If
        End If
    Next Cell
    
    ' Remove the trailing delimiter
    If Len(Result) >
0 Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    
    CompatibleTextJoin = Result
End Function

Once you save this code, you can use it in your Excel worksheet just like a standard built-in formula:

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

Advanced Trick: Combining Cells with Line Breaks

Sometimes, you want to merge cell contents vertically rather than horizontally-for example, when formatting a block mailing address label where each component sits on a new line.

To do this, you can substitute the standard comma; the line break character. In Excel, the line break character is represented by CHAR(10).

The Line Break Formula

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

Crucial Step: After applying this formula, you might notice that the text still looks flat. To see the line breaks in action, select the cell containing your formula and click the Wrap Text button on the Home tab of the Excel Ribbon.

Comparing the Methods at a Glance

Depending on your Excel environment, certain methods will be more viable than others. Use the table below to choose the right strategy for your spreadsheet:

Summary

Cleaning and organizing data doesn't have to result in messy string joins. If you are fortunate enough to use Microsoft 365 or a newer Excel version, leverage TEXTJOIN to bypass empty cells quickly. If you support users on legacy software, the nested IF statement or a robust VBA macro will keep your datasets looking neat, structured, and professional.