Spreadsheet Practice

TEXTJOIN function

Quick answer

TEXTJOIN joins a list of values with the same delimiter between each one, and can skip blanks.

Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Most common use
=TEXTJOIN("; ",TRUE,A2:A4) returns maya@acme.com; raj@globex.io (Emails for one To: line, skipping the blank)

It turns a column of emails into one semicolon-separated line, or a row of tags into "red, large, cotton", without a chain of & signs.

Available in
Excel 2019 and later, including Microsoft 365
Category
Text functions
Returns
One text string, up to 32,767 characters.
Google Sheets
Works the same in Google Sheets.

Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, ...)

ArgumentRequired?What it is
delimiterRequiredThe text to put between items, such as ", " or "; ". Use "" for none.
ignore_emptyRequiredTRUE skips empty cells. FALSE keeps them, which leaves double delimiters.
text1RequiredThe values or ranges to join. Add more after it, separated by commas.

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
E2=TEXTJOIN("; ",TRUE,A2:A4)maya@acme.com; raj@globex.ioEmails for one To: line, skipping the blank
E3=TEXTJOIN(", ",TRUE,B3:D3)blue, large, woolA row of tags as one readable list
E4=TEXTJOIN(", ",FALSE,B2:D2)red, , cottonWith FALSE, the empty tag leaves a double comma

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
EmailTag 1Tag 2Tag 3Result
maya@acme.comredcottonmaya@acme.com; raj@globex.io
raj@globex.iobluelargewoolblue, large, wool
greensmallred, , cotton

Practice TEXTJOIN

From lesson 19, Joining text. Graded instantly, then re-checked on changed data.

One-cell list

In G2, list every city from C2:C6 in one cell, separated by a comma and a space, skipping blanks.

Common mistakes

What you seeWhy, and how to fix it
#NAME? in older versionsTEXTJOIN needs Excel 2019 or later. In older versions, join with & and handle blanks with IF.
Double delimitersSet ignore_empty to TRUE. Cells where a formula returns "" are skipped too.
#VALUE! on long listsThe result can't exceed 32,767 characters, the most a cell can hold.

Tips

  • Combine it with FILTER to list only matching items: =TEXTJOIN(", ",TRUE,FILTER(A2:A20,B2:B20="East")).
  • Use CHAR(10) as the delimiter and turn on Wrap Text for one item per line.

Questions

How do I combine a column into one cell with commas?

Use =TEXTJOIN(", ",TRUE,A2:A50). TRUE skips the blank cells.

What is the difference between TEXTJOIN and CONCAT?

TEXTJOIN puts a delimiter between items and can skip blanks. CONCAT runs everything together with nothing in between.

Lessons that use TEXTJOIN

Answers that use TEXTJOIN

In other languages

TEXTJOIN is UNIRCADENAS in Spanish Excel and TEXTVERKETTEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .