How to Join Cells in Excel With TEXTJOIN (and Skip the Blanks)

TEXTJOIN lets you combine a column or a row of cells in Excel into one cell, with a comma, space or any separator you choose between each value, and skip the empty cells so you don't get gaps in the list. Here's the formula, what each part does, and the main error to watch for.

The TEXTJOIN formula and its three required parts

According to Microsoft's TEXTJOIN help page, the function "combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value." The syntax is:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

• delimiter (required): what goes between each value. Microsoft says this can be one or more characters in double quotes, a reference to a cell containing text, or an empty text string. A number is treated as text.
• ignore_empty (required): set this to TRUE and empty cells are ignored. This is the part that skips the blanks.
• text1 (required): a text string, or an array of strings such as a cell range.
• text2, … (optional): more items to join.

Microsoft's own example joins separate words with a space:

=TEXTJOIN(" ",TRUE, "The", "sun", "will", "come", "up", "tomorrow.")

That returns The sun will come up tomorrow.

How to join a column into one cell, separated by commas

1. Click the empty cell where you want the combined text.
2. Type the formula, pointing it at your range. To join A2 through A8 with a comma and a space, skipping blanks:

=TEXTJOIN(", ", TRUE, A2:A8)

3. Press Enter.

The delimiter here is ", ", a comma followed by a space, so the result reads like a normal list. Use "; " if you are pasting addresses into an email client that expects semicolons, or " " for a plain space.

The range doesn't have to be a single column. Microsoft's page also lists this example that takes in two columns at once:

=TEXTJOIN(", ", TRUE, A2:B8)

Joining a few separate cells, such as first and last names

You can list cells one by one as text1, text2 and so on instead of a range. For a first name in A2 and a last name in B2:

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

For a "City, State" string from B2 and C2:

=TEXTJOIN(", ", TRUE, B2, C2)

With ignore_empty set to TRUE, an empty cell is left out rather than joined in. Fill the formula down the column to repeat it for every row.

Skipping blanks: what TRUE does and what Microsoft doesn't spell out

Microsoft's documentation describes only the TRUE case: empty cells are ignored. For most lists, such as mailing lists, tags or names with a missing middle name, TRUE is the setting you want.

One more option from the same page: if you set the delimiter to an empty text string (""), TEXTJOIN effectively just concatenates the ranges with nothing in between.

Fixing the #VALUE! error from long results

The most common way a TEXTJOIN formula breaks on a big range is length. Microsoft says that if the result exceeds 32,767 characters, which is the cell limit, TEXTJOIN returns #VALUE!.

If you hit this:

• Join smaller ranges in separate cells, for example one TEXTJOIN per block of rows, so each result stays under the limit.
• Check whether you meant to include the whole column. A reference like an entire column can pull in far more text than you expected.
• If you'd rather show a message than an error, wrap the formula in IFERROR. See how to use IFERROR in Excel.

Which versions of Excel have TEXTJOIN

It tells Microsoft 365 subscribers to make sure they have the latest version of Office.

Going the other way, or doing this in Google Sheets

To split one cell back into several, use the reverse function: How to Split Text in Excel With TEXTSPLIT. If your data lives in Google Sheets instead, see How to Combine Cells in Google Sheets With TEXTJOIN.