How to Use TEXTJOIN Function in Excel - Practical Guide

Learn to use TEXTJOIN to combine text from multiple cells with custom delimiters and optional empty cell handling. This function is essential for consolidating data from ranges, creating dynamic lists, and generating formatted text outputs without manual concatenation. Master this formula to streamline text manipulation and improve data reporting efficiency.

Why This Matters

TEXTJOIN eliminates tedious manual text concatenation and handles empty cells automatically, saving hours on data consolidation and reporting tasks.

Prerequisites

•Basic understanding of Excel cell references (A1, ranges like A1:A10)
•Familiarity with formula syntax and how to enter formulas into cells
•Knowledge of delimiters (commas, spaces, hyphens, etc.)

Step-by-Step Instructions

Open a blank cell for the formula

Click on the cell where you want the joined text to appear, such as cell D1.

Type the TEXTJOIN formula syntax

Enter =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) where delimiter is the character(s) separating values, ignore_empty is TRUE to skip blanks or FALSE to include them, and text1 onwards are your cell ranges or individual cells.

Define your delimiter

Replace 'delimiter' with your choice in quotes, such as ", " (comma-space), "-" (hyphen), or " " (space). Use "" for no delimiter.

Set the ignore_empty parameter

Use TRUE to skip empty cells automatically, or FALSE to include them in the result (often leaving empty separator slots).

Add your data range and press Enter

Reference your data range (e.g., A1:A10) as the text parameter, then press Enter to execute the formula and view the combined result.

Alternative Methods

CONCATENATE function

Use CONCATENATE(&) to manually join cells, but requires specifying each cell individually and handling delimiters manually, making it less efficient for large ranges.

CONCAT function

CONCAT combines cells without delimiters; combine it with nested functions for delimiter handling, but lacks the automatic empty-cell filtering TEXTJOIN provides.

Ampersand (&) operator

The simple & operator concatenates values but requires manual delimiter insertion between each cell reference, becoming unwieldy with large datasets.

Tips & Tricks

✓Use TRUE for ignore_empty when working with data that has sporadic blank cells to ensure clean output without extra delimiters.
✓Combine TEXTJOIN with IF or other functions to create conditional logic for more sophisticated text combinations.
✓Test your delimiter visually first—use simple delimiters like commas or spaces before experimenting with complex formatting.
✓TEXTJOIN works across multiple ranges simultaneously: =TEXTJOIN(", ", TRUE, A1:A5, C1:C5) joins two separate ranges.

Pro Tips

★Leverage TEXTJOIN with dynamic ranges using FILTER or INDIRECT for real-time data consolidation from filtered or changing datasets.
★Nest TEXTJOIN within UPPER, LOWER, or PROPER functions to automatically format capitalization of joined text.
★Use TEXTJOIN to generate comma-separated values (CSV) exports by joining columns with commas and then copying to external files.

Troubleshooting

Formula returns #NAME? error

This error occurs in Excel versions before 2016 or non-Office 365 editions where TEXTJOIN isn't supported. Upgrade to Excel 2016+, Excel 2019, or Excel 365 for access to this function.

Output includes unwanted spaces or extra delimiters

Set ignore_empty to TRUE to automatically skip blank cells. If FALSE, blank cells still create delimiter slots, causing visual gaps in your output.

Joined text appears truncated or incomplete

Excel cells display up to 32,767 characters; TEXTJOIN can join up to this limit. Check if your result exceeds cell display width by widening the column (double-click column border) or checking formula bar for full content.

Formula works in one sheet but not another

Verify your cell references point to the correct sheet using syntax like Sheet2!A1:A10. Cross-sheet references must include the sheet name followed by an exclamation point.

Related Excel Formulas

CONCATENATE CONCAT CONCATENATE (&)TEXT TRIM PROPER UPPER LOWER FILTER INDIRECT

Frequently Asked Questions

What's the difference between TEXTJOIN and CONCATENATE?

TEXTJOIN automatically applies a delimiter between joined values and can skip empty cells with one parameter, while CONCATENATE requires manual delimiter insertion between each cell and includes empty cells as blanks in output. TEXTJOIN is far more efficient for large datasets.

Can TEXTJOIN join entire columns?

Yes, you can use entire column references like A:A, but this includes many unnecessary empty cells below your data. It's more efficient to specify a defined range like A1:A1000 to avoid processing thousands of empty cells.

What delimiters work best with TEXTJOIN?

Common delimiters include commas (","), commas with spaces (", "), semicolons (";"), hyphens ("-"), pipes ("|"), line breaks (CHAR(10)), and spaces (" "). Choose based on your output format—CSV uses commas, name lists often use spaces or hyphens.

Can I use TEXTJOIN across multiple non-adjacent ranges?

Yes, list each range as a separate parameter: =TEXTJOIN(", ", TRUE, A1:A5, C1:C5, E1:E5) joins three non-adjacent ranges into one string with comma delimiters.

Does TEXTJOIN work with numbers?

Yes, TEXTJOIN automatically converts numbers to text for joining. If you need specific number formatting (decimals, thousands separators), wrap numeric ranges with TEXT function first, such as =TEXTJOIN(", ", TRUE, TEXT(A1:A5, "0.00")).