TEXTJOIN in Excel: Join a List and Skip Blanks

TEXTJOIN can ignore blanks for you: set its second argument to TRUE and empty cells are skipped, so a list joins cleanly with no double commas. =TEXTJOIN(", ",TRUE,A2:A7) turns a column with gaps into Excel, SQL, Power BI, Python. This guide covers the syntax, how it treats different kinds of "empty", and how to join only the rows that match a condition.

How does TEXTJOIN work?#

TEXTJOIN joins a list of values and puts the same separator between each one. The separator is called the delimiter. The syntax has three parts:

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

Unlike & or CONCAT, TEXTJOIN accepts a whole range at once. That makes it the easiest way to turn a column into one line. TEXTJOIN needs Excel 2019, Excel 2021, Excel 2024 or Microsoft 365. The comparison of CONCATENATE, CONCAT and TEXTJOIN shows how the three differ.

How to make TEXTJOIN ignore blank cells#

Put TRUE as the second argument. Here is a skills column with two empty rows:

Compare the two settings:

With FALSE, each empty cell still gets a delimiter on both sides, which leaves the , , gaps. In almost every real list you want TRUE.

FALSE is useful when the position matters, for example when you build a line for another system that expects a fixed number of fields, even empty ones.

What counts as blank for TEXTJOIN?#

TEXTJOIN skips truly empty cells and cells holding empty text, but not cells holding a space. This matters with real data:

The space is the usual reason a "blank" cell still adds a comma. You can't see it, but Excel can. In Microsoft 365, trim the range inside TEXTJOIN:

=TEXTJOIN(", ",TRUE,TRIM(A2:A7))

TRIM turns a space-only cell into empty text, which TEXTJOIN then skips. In Excel 2019, enter that formula with Ctrl+Shift+Enter (Mac: Control+Shift+Return) so it works on the whole range.

How to join with a different delimiter#

The delimiter can be any text, and choosing it well makes the result easier to read. Some common choices:

The space version is great for names where the middle name is often missing. =TEXTJOIN(" ",TRUE,A2:C2) gives Maria Lopez instead of Maria Lopez with two spaces. For other ways to do this, see joining text with a space.

For the line-break version, turn on Wrap Text in the cell so each part appears on its own line. On Excel for Mac, CHAR(10) also works for a line break.

How to join only the cells that match a condition#

Put IF inside TEXTJOIN to blank out the rows you don't want, then let TRUE skip them. Say A2:A8 holds names and B2:B8 says whether each person passed:

=TEXTJOIN(", ",TRUE,IF(B2:B8="Yes",A2:A8,""))

IF returns the name where B is Yes and "" everywhere else. TEXTJOIN then joins the names and skips the empty text. In Microsoft 365 this works as a normal formula. In Excel 2019, confirm it with Ctrl+Shift+Enter.

In Excel 2021 or Microsoft 365, FILTER reads more clearly:

=TEXTJOIN(", ",TRUE,FILTER(A2:A8,B2:B8="Yes"))

Add UNIQUE to drop repeated values: =TEXTJOIN(", ",TRUE,UNIQUE(A2:A8)) lists each name once.

One thing to watch with FILTER: if no row matches, FILTER returns #CALC! and TEXTJOIN passes the error on. Give FILTER its third argument so it returns empty text instead: FILTER(A2:A8,B2:B8="Yes",""). TEXTJOIN then returns an empty cell rather than an error.

How to skip blanks without TEXTJOIN#

In Excel 2016 or earlier, there is no TEXTJOIN, so you must handle each empty cell yourself. The usual way is an IF around every part:

=A2&IF(B2="",""," "&B2)&IF(C2="",""," "&C2)

Each IF adds a space and the cell only when the cell has something in it. This works for two or three cells, such as first, middle and last names. For a long column it becomes hard to read and easy to break, which is exactly the problem TEXTJOIN solves. If your files must open in old versions, keep lists short or join them in a helper column, one row at a time.

Common TEXTJOIN problems#

Most TEXTJOIN problems come from version support, hidden characters or numbers. Check these:

1. #NAME? Your Excel is older than 2019, so it doesn't know TEXTJOIN. Use & or CONCATENATE instead. The comparison of & and CONCATENATE shows both.
2. Extra delimiters. The second argument is FALSE, or some cells hold spaces. Use TRUE and TRIM.
3. Dates show as numbers. TEXTJOIN joins a date's serial number. Wrap it: TEXT(B2,"d mmm yyyy").
4. #VALUE! The result is longer than 32,767 characters, the most one cell can hold.
5. Zeros appear. A 0 isn't empty. If a formula shows 0 for "nothing", change it to return "" instead.

Practise it#

TEXTJOIN with TRUE is one of the quickest wins in Excel, and it's easy to remember once you've typed it a few times. Practise joining text with &, CONCAT and TEXTJOIN in a live spreadsheet: you write the formula and it checks your answer instantly. The TEXTJOIN function guide is a handy syntax reference, and the guide to combining text from several cells covers the other joining methods.