Excel TEXTJOIN Function: Combine Data from Multiple Cells Fast

You need to combine 50 email addresses from individual cells into a single comma-separated list for your email client. Using CONCATENATE or the & operator means writing a formula with 50 cell references.

Or you're creating a summary report that lists all active project names from a column, but you want them in a single cell as a comma-separated string, automatically ignoring any blank cells.

The manual approach: copy each cell, paste into a text editor, add commas, repeat 50 times. That's 15 minutes of tedious work, and if the source data changes, you start over.

TEXTJOIN solves this instantly. One formula combines any range of cells with your chosen separator, automatically ignoring blanks, and updates dynamically when source data changes.

I'll show you exactly how to use TEXTJOIN, from basic combinations to advanced techniques that create dynamic lists, filter specific values, and handle complex data consolidation scenarios.

What Is TEXTJOIN and Why It Matters

TEXTJOIN is Excel's most powerful text combination function, introduced in Excel 2016 (available in Excel 2016, 2019, 2021, and Microsoft 365).

Basic syntax:

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

Parameters:

• delimiter: Character(s) to insert between combined values (comma, space, dash, etc.)
• ignore_empty: TRUE to skip blank cells, FALSE to include them
• text1, text2, ...: Cell ranges or individual cells to combine

Why TEXTJOIN beats older methods:

TEXTJOIN is the only function that can combine an entire range (A1:A50) in one reference, ignore blanks automatically, and use any separator - all in a single formula.

Basic TEXTJOIN Examples

Example 1: Combine Names with Commas

Scenario: You have a list of team members in A2:A10, and you want to create a comma-separated list in one cell.

Data:

A2: Sarah Johnson
A3: Michael Chen
A4: (blank)
A5: Rachel Martinez
A6: David Park
A7: (blank)
A8: Jennifer Lee

Formula:

1=TEXTJOIN(", ", TRUE, A2:A10)

Result:

The formula automatically skips the blank cells in A4 and A7 because ignore_empty is set to TRUE.

If you set ignore_empty to FALSE:

1=TEXTJOIN(", ", FALSE, A2:A10)

Result:

Notice the double commas where blank cells exist. Almost always use TRUE for ignore_empty.

Example 2: Create Email Distribution List

Scenario: Combine email addresses from column B into a semicolon-separated string for Outlook BCC field.

Data:

B2: sarah@company.com
B3: michael@company.com
B4: rachel@company.com
B5: david@company.com
B6: jennifer@company.com

Formula:

1=TEXTJOIN("; ", TRUE, B2:B6)

Result:

Copy this result directly into Outlook's BCC field - works perfectly.

Example 3: Combine First and Last Names from Multiple Columns

Scenario: You have first names in column A and last names in column B. Create full names in column C.

Data:

A2: Sarah    B2: Johnson
A3: Michael  B3: Chen
A4: Rachel   B4: Martinez

Formula in C2:

1=TEXTJOIN(" ", TRUE, A2, B2)

Result in C2:

Then copy the formula down to C3, C4, etc.

Why not just use =A2&" "&B2?

You can! For simple two-cell combinations, both work identically. But TEXTJOIN's real power emerges with larger ranges and conditional logic (covered later).

Advanced TEXTJOIN Techniques

Technique 1: Combine Multiple Ranges

Combine data from non-adjacent ranges in a single formula.

Scenario: Create a list of all active projects from three different columns.

Data:

Q1 Projects (C2:C5):  Q2 Projects (E2:E5):  Q3 Projects (G2:G5):
Website Redesign      Mobile App            API Integration
CRM Migration        (blank)                Dashboard Upgrade
(blank)              Email Campaign         (blank)
Security Audit       Training Program       Code Refactor

Formula:

1=TEXTJOIN(", ", TRUE, C2:C5, E2:E5, G2:G5)

Result:

All projects from three columns, blanks automatically removed, in one combined list.

Technique 2: Add Line Breaks

Create multi-line text within a single cell by using line breaks as the delimiter.

Scenario: Combine a list of tasks into a single cell with each task on a new line.

Data:

A2: Review contract
A3: Approve budget
A4: Schedule meeting
A5: Send follow-up email

Formula:

1=TEXTJOIN(CHAR(10), TRUE, A2:A5)

Result (in one cell):

Review contract
Approve budget
Schedule meeting
Send follow-up email

CHAR(10) is the line break character. For this to display correctly, enable "Wrap Text" on the cell (Home tab → Wrap Text).

Why CHAR(10) works:

• CHAR(10): Line Feed (LF) - works in Excel on Windows and Mac
• CHAR(13): Carriage Return (CR) - older Windows standard
• CHAR(13)&CHAR(10): CRLF - most compatible for external systems

For Excel internal use, CHAR(10) alone works perfectly.

Technique 3: Conditional TEXTJOIN with IF and FILTER

Combine only cells that meet specific criteria using IF or FILTER functions.

Scenario: You have a list of team members with their status (Active/Inactive). Create a list of only active members.

Data:

A2: Sarah Johnson    B2: Active
A3: Michael Chen     B3: Inactive
A4: Rachel Martinez  B4: Active
A5: David Park       B5: Active
A6: Jennifer Lee     B6: Inactive

Method 1: Using IF (works in Excel 2016+):

1=TEXTJOIN(", ", TRUE, IF(B2:B6="Active", A2:A6, ""))

Important: This is an array formula. In Excel 2019 and earlier, press Ctrl+Shift+Enter after typing the formula. In Microsoft 365, just press Enter (dynamic arrays handle it automatically).

Result:

How it works:

1. IF(B2:B6="Active", A2:A6, "") creates an array: {"Sarah Johnson", "", "Rachel Martinez", "David Park", ""}
2. TEXTJOIN combines the array, ignoring empty strings
3. Result: only active members

Method 2: Using FILTER (Microsoft 365 only):

1=TEXTJOIN(", ", TRUE, FILTER(A2:A6, B2:B6="Active"))

This is cleaner and doesn't require array formula entry. FILTER creates an array of only the active members, which TEXTJOIN then combines.

Technique 4: Combine with Bullet Points

Create formatted bullet-point lists within a cell.

Scenario: Combine project deliverables into a bulleted list.

Data:

A2: Design mockups
A3: Database schema
A4: API documentation
A5: Testing plan

Formula:

1=TEXTJOIN(CHAR(10), TRUE, "• " & A2:A6)

Important: This requires Microsoft 365 with dynamic array support. For earlier versions:

1="• " & TEXTJOIN(CHAR(10) & "• ", TRUE, A2:A6)

Result (with Wrap Text enabled):

• Design mockups
• Database schema
• API documentation
• Testing plan

Technique 5: Create Dynamic Drop-Down Lists

Use TEXTJOIN to create comma-separated values for data validation lists.

Scenario: Create a dynamic dropdown list of active products from a table that changes frequently.

Data (Products sheet):

A2: Product A    B2: Active
A3: Product B    B3: Discontinued
A4: Product C    B4: Active
A5: Product D    B5: Active

Helper cell formula (Products!D2):

1=TEXTJOIN(",", TRUE, IF(B2:B100="Active", A2:A100, ""))

Press Ctrl+Shift+Enter (Excel 2019 and earlier) or just Enter (Microsoft 365).

Create dropdown in another sheet:

1. Select cell where you want dropdown
2. Data tab → Data Validation
3. Allow: List
4. Source: =Products!$D$2

Now your dropdown automatically includes only active products, updating as you change statuses in column B.

TEXTJOIN with Other Functions

Combine TEXTJOIN with UNIQUE

Microsoft 365 only: Remove duplicates while combining.

Scenario: You have a list of departments with duplicates. Create a unique list.

Data:

A2: Sales
A3: Marketing
A4: Sales
A5: IT
A6: Marketing
A7: Sales

Formula:

1=TEXTJOIN(", ", TRUE, UNIQUE(A2:A7))

Result:

Each department appears only once, even though Sales and Marketing appeared multiple times in the source data.

Combine TEXTJOIN with SORT

Microsoft 365 only: Combine values in alphabetical order.

Scenario: Create an alphabetically sorted list of team members.

Data:

A2: Michael
A3: Sarah
A4: David
A5: Rachel
A6: Jennifer

Formula:

1=TEXTJOIN(", ", TRUE, SORT(A2:A6))

Result:

Combine TEXTJOIN with COUNTIF for Lists

Create a descriptive list showing counts.

Scenario: Show how many times each status appears.

Data:

A2: Active
A3: Inactive
A4: Active
A5: Active
A6: Pending
A7: Active
A8: Inactive

Formula:

1="Active: " & COUNTIF(A2:A8,"Active") & ", Inactive: " & COUNTIF(A2:A8,"Inactive") & ", Pending: " & COUNTIF(A2:A8,"Pending")

Result:

This isn't pure TEXTJOIN but demonstrates combining TEXTJOIN-style logic with other functions for summary reports.

Real-World Use Cases

Use Case 1: Sales Territory Summary

Problem: Create a summary showing all sales reps assigned to each territory.

Data:

Rep (A)           Territory (B)
Sarah Johnson     West
Michael Chen      East
Rachel Martinez   West
David Park        Central
Jennifer Lee      West
Marcus Torres     East

Formula for West Territory (in separate summary section):

1=TEXTJOIN(", ", TRUE, IF($B$2:$B$7="West", $A$2:$A$7, ""))

Result:

Create similar formulas for East and Central territories. Now you have a clean territory assignment summary.

Use Case 2: Project Status Report

Problem: Create a one-line summary of all in-progress projects for a weekly status email.

Data:

Project (A)           Status (B)      Owner (C)
Website Redesign      In Progress     Sarah
Mobile App           Complete         Michael
CRM Migration        In Progress      Rachel
API Integration      In Progress      David
Dashboard Upgrade    On Hold          Jennifer

Formula:

1="In Progress: " & TEXTJOIN(", ", TRUE, IF($B$2:$B$6="In Progress", $A$2:$A$6, ""))

Result:

Copy this directly into your status email. Updates automatically when project statuses change.

Use Case 3: Inventory Low Stock Alert

Problem: Create a comma-separated list of products below reorder point for purchasing team.

Data:

Product (A)          Stock (B)    Reorder Point (C)
Widget A             150          100
Widget B             45           50
Widget C             200          100
Widget D             15           50
Widget E             5            20

Formula:

1="Low Stock Items: " & TEXTJOIN(", ", TRUE, IF($B$2:$B$6<$C$2:$C$6, $A$2:$A$6, ""))

Result:

Set up conditional formatting to highlight this cell in red when it contains text, creating an automated alert system.

Performance and Limitations

Performance Considerations

TEXTJOIN is fast for normal use cases:

• Combining 100 cells: < 0.01 seconds
• Combining 1,000 cells: < 0.1 seconds
• Combining 10,000 cells: 1-2 seconds

Performance degrades with:

• Array formulas (IF or FILTER inside TEXTJOIN)
• Very large ranges (50,000+ cells)
• Complex nested functions

Optimization tips:

1. Use helper columns for complex conditions:

Instead of:

1=TEXTJOIN(", ", TRUE, IF(AND($B$2:$B$100="Active", $C$2:$C$100>1000), $A$2:$A$100, ""))

Use:

1Column D: =IF(AND(B2="Active", C2>1000), A2, "")
2Main formula: =TEXTJOIN(", ", TRUE, D2:D100)

This is faster and easier to debug.

1. Limit range size: Don't use A:A (entire column). Use specific ranges like A2:A1000.

2. Avoid volatile functions: INDIRECT and OFFSET inside TEXTJOIN cause recalculation on every sheet change.

Limitations

Character limit: TEXTJOIN output is limited by Excel's 32,767 character cell limit. Combining 10,000 cells with average length 50 characters exceeds this limit.

Workaround for huge lists: Split into multiple TEXTJOIN formulas:

1Cell E1: =TEXTJOIN(", ", TRUE, A2:A1000)
2Cell E2: =TEXTJOIN(", ", TRUE, A1001:A2000)
3Cell E3: =E1 & ", " & E2

Excel version compatibility: TEXTJOIN requires:

• Excel 2016 or later
• Excel Online
• Excel for Mac 2016 or later

Not available in Excel 2013 or earlier. For older versions, use VBA or upgrade to Microsoft 365.

TEXTJOIN vs. Alternatives

When to use TEXTJOIN

✅ Combining ranges (more than 3-4 cells) ✅ Need to ignore blanks automatically ✅ Want custom delimiters ✅ Data updates frequently (dynamic) ✅ Combining with conditional logic (IF, FILTER)

When to use & operator

✅ Combining 2-3 specific cells ✅ Complex formatting with mix of text and numbers ✅ Need maximum backward compatibility (works in all Excel versions)

Example:

1="Name: " & A2 & ", Age: " & B2 & ", Department: " & C2

When to use CONCAT

✅ Simple concatenation without delimiter ✅ Need backward compatibility (CONCAT works in Excel 2016+, while TEXTJOIN also requires 2016+)

CONCAT doesn't ignore blanks and doesn't accept a delimiter, making it less useful than TEXTJOIN for most scenarios.

Troubleshooting Common Issues

Issue 1: Formula returns #NAME? error

Cause: Excel version doesn't support TEXTJOIN (Excel 2013 or earlier)

Solution: Upgrade to Excel 2016 or later, or use VBA alternative:

1Function TextJoinVBA(delimiter As String, ignoreEmpty As Boolean, ParamArray arr() As Variant) As String
2    Dim result As String
3    Dim item As Variant
4    
5    For Each item In arr
6        If IsArray(item) Then
7            Dim cell As Variant
8            For Each cell In item
9                If Not ignoreEmpty Or Len(cell) > 0 Then
10                    If Len(result) > 0 Then result = result & delimiter
11                    result = result & cell
12                End If
13            Next cell
14        Else
15            If Not ignoreEmpty Or Len(item) > 0 Then
16                If Len(result) > 0 Then result = result & delimiter
17                result = result & item
18            End If
19        End If
20    Next item
21    
22    TextJoinVBA = result
23End Function

Use in Excel as: =TextJoinVBA(", ", TRUE, A2:A10)

Issue 2: Array formula not working with IF

Symptom: Formula returns only first cell's value instead of combining all cells

Cause: Forgot to press Ctrl+Shift+Enter for array formula entry (Excel 2019 and earlier)

Solution:

• Excel 2019 and earlier: Select the cell with the formula, click in formula bar, press Ctrl+Shift+Enter. You'll see curly braces { } around the formula.
• Microsoft 365: Just press Enter normally (dynamic arrays handle this automatically)

Issue 3: Line breaks don't display

Symptom: CHAR(10) appears as a square or space instead of line break

Solution: Enable "Wrap Text" for the cell

1. Select cell
2. Home tab → Wrap Text

Or use Alt+Enter when in edit mode to test if line breaks work.

Issue 4: Result truncated at 32,767 characters

Symptom: Combined text cuts off mid-word

Cause: Excel cell character limit (32,767 characters)

Solution: Split into multiple cells or use Power Query for truly massive text combinations.

Conclusion

TEXTJOIN is the most powerful text combination function in Excel, eliminating the tedious work of manually concatenating data from multiple cells.

Key use cases:

• Combine email lists for distribution
• Create comma-separated summary lists
• Build dynamic dropdown values
• Generate formatted text with line breaks or bullets
• Conditionally combine data with IF or FILTER

Remember:

• Always use ignore_empty = TRUE to skip blanks
• Use CHAR(10) for line breaks (with Wrap Text enabled)
• Combine with IF for conditional lists (Ctrl+Shift+Enter in Excel 2019 and earlier)
• Use FILTER in Microsoft 365 for cleaner conditional formulas

Start using TEXTJOIN today to eliminate repetitive copy-paste workflows and create dynamic, automatically-updating text combinations.

Frequently Asked Questions

Does TEXTJOIN work with numbers, or only text?

TEXTJOIN works with both numbers and text. It automatically converts numbers to text when combining them. For example, =TEXTJOIN(", ", TRUE, A1:A5) works whether those cells contain "Apple", "Banana" or 100, 200, 300. The output will be text format regardless of input data types.

Can I use TEXTJOIN with cells from different sheets?

Yes. Reference ranges from other sheets using standard notation: =TEXTJOIN(", ", TRUE, Sheet2!A2:A10, Sheet3!B5:B15). You can combine data from multiple sheets in a single TEXTJOIN formula. Just ensure sheet names with spaces are in single quotes: 'Sales Data'!A2:A10.

How do I remove duplicates when using TEXTJOIN?

In Microsoft 365, wrap your range with UNIQUE: =TEXTJOIN(", ", TRUE, UNIQUE(A2:A10)). In Excel 2019 and earlier, use a helper column with COUNTIF to mark duplicates, then use IF in TEXTJOIN to exclude them, or use Remove Duplicates feature on your source data before applying TEXTJOIN.

Can TEXTJOIN handle more than one delimiter?

Not directly. TEXTJOIN uses a single delimiter throughout. However, you can use SUBSTITUTE after TEXTJOIN to replace specific delimiters: =SUBSTITUTE(TEXTJOIN("|", TRUE, A1:A5), "|3|", "|THREE|"). This replaces the 3rd item's delimiters with different characters. Alternatively, build complex strings using nested TEXTJOIN formulas.

Why does my TEXTJOIN with IF return only one value instead of all matching values?

You forgot to enter it as an array formula. In Excel 2019 and earlier, press Ctrl+Shift+Enter after typing the formula (you'll see { } braces appear). In Microsoft 365 with dynamic arrays, this happens automatically - just press Enter. Alternatively, use FILTER instead of IF for cleaner syntax in Microsoft 365.

Related articles: Seven Excel Formulas Every Professional Should Know, Master XLOOKUP: Excel's New Lookup Formula