Home / Tutorials

Intermediate 10 min read Updated 26/10/2024

How to Use TEXTJOIN Function in Excel

Learn how to combine text from multiple cells using Excel's TEXTJOIN function, a powerful tool for concatenating ranges of cells with custom delimiters while handling empty cells intelligently.

In this tutorial:

Basic TEXTJOIN syntax and usage
Combining text with different delimiters
Handling empty cells and arrays
Advanced usage and examples
Alternatives for older Excel versions

You'll need:

Excel 2019 or Microsoft 365
Text data to combine
Basic Excel knowledge

How to Use TEXTJOIN in Excel

Understanding TEXTJOIN Function

Basic Syntax:


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

• delimiter: The character(s) to insert between text values

• ignore_empty: TRUE to skip empty cells, FALSE to include them

• text1, text2, etc.: Text items or ranges to join

Basic Examples

Example 1: Joining Names

Sample Data:

A	B	C
John	M.	Smith

Formula:


                            =TEXTJOIN(" ", TRUE, A1:C1)

Result: John M. Smith

Example 2: Creating CSV Data

Sample Data:

A	B	C
Apple	Orange	Banana

Formula:


                            =TEXTJOIN(",", TRUE, A1:C1)

Result: Apple,Orange,Banana

Advanced Usage

Combining Multiple Ranges


                    =TEXTJOIN(", ", TRUE, A1:A5, C1:C5, E1:E5)

Creating Custom Lists


                    =TEXTJOIN(CHAR(10), TRUE, "• " & A1:A10)

Real-World Applications

Email List Creation


                    =TEXTJOIN(";", TRUE, A2:A100)

Perfect for creating email distribution lists

URL Parameters


                    ="https://example.com/?" & TEXTJOIN("&", TRUE, A2:A10)

Builds URL strings with parameters

Common Mistakes and Solutions

Common Issues:

Character Limit Error
Excel has a 32,767 character limit for text cells
Wrong Data Types
Numbers need to be converted to text first

Alternative Methods for Older Excel Versions

Using CONCATENATE:


                        =CONCATENATE(A1, " ", B1, " ", C1)

Using & Operator:


                        =A1 & " " & B1 & " " & C1