Joining With Separators Using TEXTJOIN

In the last lesson you saw that CONCAT can join a range but cannot put a separator between each value.

Imagine you have a column of cities and you want them as one comma-separated list: Paris, Tokyo, Cairo. Doing that with CONCAT would mean typing each comma by hand.

TEXTJOIN solves this elegantly: you give it one delimiter, and it places that delimiter between every value automatically.

TEXTJOIN has three parts:

• delimiter the text placed between values, like ", "
• ignore_empty TRUE to skip blank cells, FALSE to keep them
• text1, text2, ... the cells or ranges to join

The shape is =TEXTJOIN(delimiter, ignore_empty, text1, ...). The delimiter is used only between items, never at the very start or end.

Suppose A2:A4 holds Paris, Tokyo, and Cairo.

The formula below joins them with a comma and a space between each, giving Paris, Tokyo, Cairo.

Notice there is no comma before Paris or after Cairo. TEXTJOIN only inserts the delimiter between values.

The delimiter can be any text you like. Common choices include:

• ", " for a readable list
• " - " for a dash-separated path
• "; " for semicolon lists
• " " for a single space (like building a full name)

Here we join name parts with a single space.

The ignore_empty argument is powerful. Set it to TRUE and any blank cells in your range are skipped, so you never get double delimiters.

Suppose A2:A4 holds Paris, a blank cell, and Cairo. With ignore_empty set to TRUE you get Paris, Cairo, not Paris, , Cairo.

Sometimes you actually want to keep the empty slots, for example to line up data positions.

Set ignore_empty to FALSE and blank cells still produce a delimiter. With A2:A4 holding Paris, blank, Cairo, the result becomes Paris, , Cairo.

Choose TRUE for clean lists and FALSE when positions matter.

Say a row holds an order: ID in A2, item in B2, quantity in C2, and price in D2.

To export it as a comma-separated line, join the whole range with a comma delimiter.

The result might be 1001,Notebook,3,4.99, ready to paste into a CSV file.

TEXTJOIN can take several ranges and individual cells as separate arguments, all sharing the same delimiter.

For example, combine two separate columns of tags into one list:

Every value from both ranges is joined in order with your chosen delimiter between them.

You can stack values onto separate lines inside one cell by using a line-break character as the delimiter.

In Excel that character is CHAR(10). In Google Sheets you can also use CHAR(10).

After entering the formula, turn on wrap text for the cell so the line breaks are visible.

You have a column of email addresses in A2:A10 and want them as one semicolon-separated string to paste into an email's To field.

Use a semicolon-and-space delimiter and skip any blanks so the list stays clean.

The output is a single ready-to-paste recipient line that updates as you add addresses.

Here is the quick decision guide:

• Use CONCAT or & when you join a few specific items with different or no separators.
• Use TEXTJOIN when you join a range and want the same delimiter between each value, especially with blanks to skip.

TEXTJOIN shines for lists, CSV rows, and tag strings where typing each separator would be tedious.

Test what you know about TEXTJOIN.

You now know how to build delimited lists:

• =TEXTJOIN(delimiter, ignore_empty, text1, ...)
• The delimiter goes between values, never at the ends.
• ignore_empty = TRUE skips blanks for clean lists.
• It accepts ranges, so one formula handles a whole column.
• Use CHAR(10) for line breaks inside a cell.

Next, you will standardize the capitalization of text with UPPER, LOWER, and PROPER.