How to fix #N/A error in TEXTJOIN Function in Excel?

The #N/A error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #REF! error in TEXTJOIN Function in Excel?

The #REF! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #VALUE! error in TEXTJOIN Function in Excel?

The #VALUE! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #DIV/0! error in TEXTJOIN Function in Excel?

The #DIV/0! error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

How to fix #NAME? error in TEXTJOIN Function in Excel?

The #NAME? error typically occurs when there's an issue with the formula parameters or references. Use AskFormulas to generate validated formulas that avoid these errors.

TEXTJOIN Function in Excel

Master TEXTJOIN to combine text from multiple cells with custom delimiters. Merge data, skip blanks, and create lists in Excel 2019+ and Sheets.

Excel

Google Sheets

text

intermediate

Syntax Preview

Excel

Google Sheets

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

Quick Answer

Comprehensive Explanation

How to Use TEXTJOIN - Step by Step

## What is the TEXTJOIN Function?

The TEXTJOIN function is a powerful text manipulation tool introduced in Excel 2019 and Office 365, also available in Google Sheets. It revolutionizes how we combine text by allowing you to join multiple text strings with a delimiter of your choice, while intelligently handling empty cells.

### Core Functionality

At its core, TEXTJOIN operates using a three-step methodology:

1. **Collection Phase:** Gathers all specified text values from cells, ranges, or direct input
2. **Filtering Phase:** Optionally removes empty cells based on the ignore_empty parameter
3. **Joining Phase:** Combines all remaining values using the specified delimiter

This process ensures consistent formatting, maintains data integrity, and provides flexible output options. The function can handle up to 252 text arguments and processes ranges efficiently.

### When to Use TEXTJOIN

TEXTJOIN is optimal for:

**Data Consolidation:** When you need to combine values from multiple cells into a single cell, such as creating full names from first and last name columns or building addresses from component parts.

**List Creation:** For generating comma-separated lists from columnar data, perfect for email lists, inventory codes, or tag systems.

**Dynamic Reporting:** In situations where you need to create narrative text that incorporates values from various cells, maintaining readability while ensuring data accuracy.

### Key Advantages Over Alternatives

While functions like CONCATENATE and CONCAT serve similar purposes, TEXTJOIN distinguishes itself through:

- **Delimiter Management:** Unlike CONCATENATE, you specify the delimiter once rather than between each value
- **Range Support:** Can process entire ranges instead of individual cells
- **Empty Cell Handling:** Built-in option to skip blank cells automatically
- **Cleaner Syntax:** More readable formulas, especially with multiple values

### Platform Compatibility

**Excel 365:** Full support including dynamic array compatibility
**Excel 2019+:** Standard implementation with all features
**Google Sheets:** Complete support with identical syntax
**Excel 2016 and earlier:** Not available - use CONCATENATE as alternative

Note: In older Excel versions, you'll see a #NAME? error. Consider upgrading or using workaround formulas.

## Step-by-Step Guide

### Step 1: Prepare Your Data

Before using TEXTJOIN, organize your data in a logical structure. Place the values you want to combine in adjacent cells or defined ranges. This could be names in columns A and B, or product codes scattered across different cells. Ensure your data is clean and formatted consistently for best results.

### Step 2: Choose Your Delimiter

Select an appropriate delimiter based on your output needs. Common choices include:
- Comma and space (", ") for lists
- Space (" ") for combining names
- Hyphen ("-") for codes or IDs
- Line break (CHAR(10)) for vertical lists
- Pipe ("|") for database exports

### Step 3: Decide on Empty Cell Handling

Determine whether blank cells should be included or skipped. Use TRUE to ignore empty cells and create clean, continuous lists. Use FALSE when you need to preserve the position of values, even if some are missing.

### Step 4: Enter the Formula

Click on the cell where you want the result and type:
`=TEXTJOIN(", ", TRUE, A1:A10)`

Replace the delimiter, ignore_empty value, and range according to your needs.

### Step 5: Extend for Multiple Ranges

To combine non-adjacent ranges, add them as additional arguments:
`=TEXTJOIN(", ", TRUE, A1:A10, C1:C10, E1:E10)`

This flexibility allows you to pull data from various parts of your worksheet.

Practical Examples

Common Errors and Solutions

#NAME?

Excel doesn't recognize the TEXTJOIN function

Cause:

TEXTJOIN is not available in Excel versions before 2019. The function doesn't exist in Excel 2016, 2013, or earlier versions.

Solution:

1. Upgrade to Excel 2019, Excel 365, or use Google Sheets 2. Use alternative formulas like CONCATENATE or CONCAT 3. Create a custom VBA function to replicate TEXTJOIN functionality 4. Use Power Query to join text values

Prevention:

Check your Excel version before using TEXTJOIN. Go to File > Account to see your Office version.

Frequency: 35%

Example:

#VALUE!

TEXTJOIN returns a VALUE error

Cause:

The delimiter parameter is missing, invalid, or the result exceeds Excel's 32,767 character limit for a single cell.

Solution:

1. Ensure the delimiter is in quotes: TEXTJOIN(",", TRUE, ...) 2. Check if your combined text exceeds 32,767 characters 3. Verify all parameters are in the correct order 4. Split large datasets into smaller chunks

Prevention:

Always enclose text delimiters in quotes and monitor the length of combined text

Frequency: 25%

Example:

#SPILL!

Spill error when using TEXTJOIN with dynamic arrays

Cause:

In Excel 365, TEXTJOIN might conflict with dynamic array formulas, or there's data blocking the spill range.

Solution:

1. Clear cells below the formula to allow spilling 2. Use @ operator to prevent spilling: =@TEXTJOIN(...) 3. Convert dynamic arrays to static values first 4. Ensure no merged cells are in the spill area

Prevention:

Keep the area below dynamic formulas clear or use the @ operator when spilling is not desired

Frequency: 15%

Example:

Missing Values

Some text values are not included in the result

Cause:

The ignore_empty parameter is set to TRUE and cells contain spaces or formulas returning empty strings.

Solution:

1. Use FALSE for ignore_empty if you need all positions 2. Clean data with TRIM to remove spaces 3. Check if cells contain formulas returning "" 4. Use ISBLANK to verify truly empty cells

Prevention:

Understand the difference between empty cells and cells with spaces or zero-length strings

Frequency: 20%

Example:

Best Practices and Advanced Techniques

Combine TEXTJOIN with dynamic functions like FILTER, UNIQUE, or SORT for powerful data manipulation. For example, create a list of unique values: =TEXTJOIN(", ", TRUE, UNIQUE(A:A))

For large datasets (>10,000 cells), limit range references instead of using entire columns. Use A2:A10000 instead of A:A to improve calculation speed by up to 10x.

Remember that Excel cells can only contain 32,767 characters. When joining large amounts of text, monitor the total length to avoid truncation. Consider splitting into multiple cells if needed.

TEXTJOIN works identically in Excel 2019+, Excel 365, and Google Sheets. However, some combined functions like FILTER may have platform-specific syntax differences.

Always wrap TEXTJOIN in IFERROR when working with dynamic data sources to handle potential errors gracefully, especially when combining with other functions.

TEXTJOIN vs Alternative Functions

Related Formulas

## Comparison with Alternative Functions

### TEXTJOIN vs CONCATENATE

| Feature | TEXTJOIN | CONCATENATE | Winner |
|---------|----------|-------------|--------|
| Delimiter handling | Once for all values | Between each value | TEXTJOIN |
| Range support | Full ranges (A1:A100) | Individual cells only | TEXTJOIN |
| Empty cell handling | Built-in option | Manual checking needed | TEXTJOIN |
| Availability | Excel 2019+ | All Excel versions | CONCATENATE |
| Maximum arguments | 252 | 255 | Similar |
| Formula complexity | Simple and clean | Can become lengthy | TEXTJOIN |

### TEXTJOIN vs CONCAT

While CONCAT (introduced in Excel 2016) can handle ranges, it lacks delimiter support and empty cell handling, making TEXTJOIN superior for most text-joining tasks.

### TEXTJOIN vs Ampersand ( ) Operator

The   operator is universal and simple for joining 2-3 values, but becomes unwieldy with multiple values and offers no empty cell handling.

### When to Use Each

**Use TEXTJOIN when:**
- Working with Excel 2019+ or Google Sheets
- Joining multiple values with consistent delimiters
- Needing to skip empty cells automatically
- Dealing with ranges of data

**Use CONCATENATE/CONCAT when:**
- Working with older Excel versions
- Joining just a few specific cells
- Delimiter requirements vary between values

**Use   operator when:**
- Creating simple combinations of 2-3 values
- Building formulas with mixed text and cell references
- Maximum compatibility is required

Related Formulas

CONCATENATE Function

Master CONCATENATE to join text in Excel and Google Sheets. Learn syntax, examples, errors, and modern alternatives like CONCAT and TEXTJOIN.

beginner

text

Excel

Validated

View Details

FILTER Function in Excel

Master the FILTER function to dynamically extract data meeting specific criteria. Learn syntax, examples, and error solutions for Excel 365 and Sheets.

intermediate

array

Excel

Validated

View Details

UNIQUE Function

The UNIQUE function returns unique values from a range or array, eliminating duplicates automatically. Perfect for creating distinct lists from data.

intermediate

array

Excel

Validated

View Details

LEFT Function in Excel

Extract text from the beginning of strings with the LEFT function. Learn syntax, examples, and error solutions for Excel and Google Sheets.

beginner

text

Excel

Validated

View Details