How to How to Use TEXTJOIN with Non-Blank Cells Only in Excel - Practical Guide

Learn to use TEXTJOIN to combine text from multiple cells while automatically excluding blank cells. This formula is essential for creating clean, consolidated text strings from ranges with inconsistent data. You'll master filtering empty values without nested IF statements, saving time on data cleaning tasks.

Why This Matters

Efficiently combines data from multiple sources while handling missing values automatically, crucial for data consolidation and reporting tasks.

Prerequisites

•Basic understanding of cell references and ranges
•Familiarity with Excel formula syntax
•Excel 2016 or later (TEXTJOIN availability)

Step-by-Step Instructions

Open a blank cell for your formula

Click on the cell where you want the combined text result to appear (e.g., cell E2).

Type the TEXTJOIN opening

Enter =TEXTJOIN( to start the formula. This function accepts delimiter, ignore_empty, and text_array parameters.

Set the delimiter parameter

Specify your delimiter in quotes (e.g., ", " or " ") between commas, then type a comma. This separates combined values.

Enable the ignore_empty parameter

Type TRUE after the second comma. This automatically skips blank cells without manual filtering.

Define your range and press Enter

Specify your cell range (e.g., A2:D2), close with ), and press Enter. The formula combines non-blank cells with your chosen delimiter.

Alternative Methods

CONCAT with IF array formula

Use CONCAT combined with IF as an array formula {=CONCAT(IF(range<>"",range,""))} to filter blanks, though TEXTJOIN is cleaner.

FILTER function (Excel 365)

In newer versions, use =TEXTJOIN(",",TRUE,FILTER(range,range<>"")) for dynamic blank exclusion with more flexibility.

Tips & Tricks

✓Use empty string "" as delimiter if you want no separator between combined values.
✓Wrap TEXTJOIN in TRIM() to remove extra spaces: =TRIM(TEXTJOIN(...)) for cleaner output.
✓Test with a small range first before applying to large datasets.

Pro Tips

★Combine TEXTJOIN with IF to add conditional logic: =TEXTJOIN(", ",TRUE,IF(A:A>100,A:A,"")).
★Use semicolon delimiter for European locales where comma is the decimal separator.
★Reference entire columns in TEXTJOIN to future-proof your formula against data additions.

Troubleshooting

Formula returns #NAME? error

TEXTJOIN isn't available in Excel versions before 2016. Update Excel or use CONCAT with IF array formula alternative.

Blank cells still appear in results

Verify the second parameter is TRUE (not FALSE). Check that cells are truly empty and not containing spaces or formulas returning empty strings.

Extra spaces between combined values

Wrap the entire TEXTJOIN formula in TRIM() function to remove leading/trailing spaces from the result.

Related Excel Formulas

CONCAT CONCATENATE FILTER IF TRIM SUBSTITUTE

Frequently Asked Questions

Can TEXTJOIN handle entire column references like A:A?

Yes, TEXTJOIN works with entire column references. However, this may impact performance on large datasets, so consider limiting to specific ranges when possible.

What's the difference between TRUE and FALSE in the ignore_empty parameter?

TRUE skips blank cells entirely (recommended for clean output). FALSE includes blanks as empty strings, creating unnecessary delimiters between values.

Can I use TEXTJOIN with multiple criteria or conditions?

Nest IF statements within TEXTJOIN: =TEXTJOIN(", ",TRUE,IF((A:A>100)*(B:B="Yes"),A:A,"")) for multi-condition filtering of results.