Combining text in Excel has traditionally been a cumbersome process, often requiring multiple functions or complex workarounds. The TEXTJOIN function solves this challenge by offering a streamlined way to combine text with custom delimiters while intelligently handling empty cells. Available in Excel 2019 and later versions, TEXTJOIN provides significant advantages over traditional concatenation methods, especially when working with large datasets.
Combine Text with TEXTJOIN: Step-by-Step Tutorial
Let’s start with the basic syntax of TEXTJOIN:
Copy
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Where:
- delimiter: The character(s) to insert between each text value
- ignore_empty: TRUE or FALSE to specify whether to skip empty cells
- text1, text2, etc.: The text items to join (cells, ranges, or text strings)
Basic Example: Customer Information
Let’s combine customer information into a single cell:
|
Column A |
Column B |
Column C |
|---|---|---|
|
John |
Smith |
New York |
|
Jane |
Doe |
Chicago |
Command: Combine name and city with commas
Copy
=TEXTJOIN(“, “, TRUE, A1, B1, C1)
Result: “John, Smith, New York”
Adding Space Between Combined Text
When formatting names or addresses, proper spacing is crucial.
Command: Create full names with proper spacing
Copy
=TEXTJOIN(” “, TRUE, A2:B2)
For multiple rows:
|
First |
Last |
Result |
|---|---|---|
|
John |
Smith |
John Smith |
|
Jane |
Doe |
Jane Doe |
Creating Comma-Separated Lists
TEXTJOIN excels at creating CSV-style outputs:
Command: Create a list of products
Copy
=TEXTJOIN(“, “, TRUE, A2:A10)
Example with product data:
|
Products |
|---|
|
Laptop |
|
Mouse |
|
Keyboard |
[empty cell]
Monitor
Result: “Laptop, Mouse, Keyboard, Monitor”
Joining Text with Custom Delimiters
TEXTJOIN supports various delimiter types:
- Using symbols:
Copy
=TEXTJOIN(” | “, TRUE, A2:C2)
- Multiple characters:
Copy
=TEXTJOIN(” –> “, TRUE, A2:C2)
- Line breaks:
Copy
=TEXTJOIN(CHAR(10), TRUE, A2:C2)
TEXTJOIN vs. CONCATENATE Comparison
Feature comparison:
|
Feature |
TEXTJOIN |
CONCATENATE |
|---|---|---|
|
Delimiter support |
Yes 
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule. Get Started |
No |
|
Handle empty cells |
Yes |
No |
|
Range support |
Yes |
No |
|
Max arguments |
252 |
30 |
Example showing difference:
Copy
TEXTJOIN: =TEXTJOIN(“, “, TRUE, A2:C2)
CONCATENATE: =CONCATENATE(A2, “, “, B2, “, “, C2)
Combining Multiple Ranges with TEXTJOIN
Working with non-adjacent ranges:
Command: Combine data from different worksheets
Copy
=TEXTJOIN(” “, TRUE, Sheet1!A2:A5, Sheet2!B2:B5)
Dynamic range example:
Copy
=TEXTJOIN(“, “, TRUE, INDIRECT(“A2:A” & COUNTA(A:A)))
Common TEXTJOIN Applications
- Email List Creation:
Copy
=TEXTJOIN(“; “, TRUE, A2:A100)
- Address Formatting:
Copy
=TEXTJOIN(“, “, TRUE, A2, B2, C2, D2)
- Product Description Building:
Copy
=TEXTJOIN(” – “, TRUE, A2, B2, C2)
Advanced TEXTJOIN Techniques
- Nested Functions:
Copy
=TEXTJOIN(“, “, TRUE, UPPER(A2:A5))
- Conditional Joining:
Copy
=TEXTJOIN(“, “, TRUE, IF(A2:A10>0, A2:A10, “”))
- Array Formulas:
Copy
=TEXTJOIN(CHAR(10), TRUE, IF(A2:A10=”Yes”, B2:B10, “”))
Putting It All Together
TEXTJOIN’s versatility makes it an essential tool for data manipulation in Excel. From simple text combination to complex data formatting, its ability to handle empty cells and work with ranges streamlines many common spreadsheet tasks. Remember to verify your Excel version supports TEXTJOIN before implementing these solutions.
Ready to take your spreadsheet automation to the next level? Discover how Coefficient can help you seamlessly integrate live data from your business systems directly into Excel. Get started with Coefficient today and transform your data management workflow.
