TEXTJOIN in Google Sheets joins text from several cells or a whole range into one cell, with a separator of your choice between each piece. By the end of this guide you'll be able to build a comma-separated list, merge first and last names, and build an address line that doesn't break when some cells are empty.
The TEXTJOIN syntax in one line
Google's TEXTJOIN help page gives the syntax as:
TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])
Here is what each part does, per that page:
- delimiter: the text placed between each item, such as
", "or" ". It can be a string, a reference to a cell holding a string, or empty. If it's empty (""), the items are simply run together. - ignore_empty:
TRUEorFALSE. WithTRUE, empty cells in your ranges are left out of the result. - text1: a single piece of text, or a range of cells.
- text2, ...: optional additional text items.
The second argument is the one that matters most. Set it to TRUE unless you have a reason not to.
Join a range into a comma-separated list
- Click the cell where you want the combined text.
- Type the formula, pointing it at your range:
=TEXTJOIN(", ", TRUE, A2:A6)
- Press Enter.
Every non-empty cell in A2:A6 appears in one cell, separated by a comma and a space. Swap ", " for " ", " | " or any other string to change the separator.
Google's own sample uses FALSE here: TEXTJOIN(", ", FALSE, A1:A5). That works when every cell is filled, but read the next section before copying it.
Why blank cells add extra separators, and the fix
This is the most common problem with TEXTJOIN: the result has doubled-up commas or spaces. The cause is almost always ignore_empty set to FALSE.
Google's help page shows it with this data in A2:A6: Hello, there, (blank), (blank), Sheets.
| Formula | Blank cells |
|---|---|
=TEXTJOIN(" ", TRUE, A2:A6) |
Left out; the result is Hello there Sheets |
=TEXTJOIN(" ", FALSE, A2:A6) |
Not left out |
With TRUE, the blanks are skipped and the separators line up.
The fix: change the second argument to TRUE.
Use FALSE only when the gaps carry meaning, for example when another program reads your output and expects every position to be present even if empty.
Merge first and last names
If first names are in column A and last names in column B, put this in C2:
=TEXTJOIN(" ", TRUE, A2, B2)
Because ignore_empty is TRUE, a row with only a first name won't end in a trailing space. To fill the rest of the column, copy C2 down alongside your data.
To get "Last, First" instead, reorder the arguments and change the separator:
=TEXTJOIN(", ", TRUE, B2, A2)
Build an address line that skips missing parts
Addresses are where skipping blanks pays off. Say a row has street in A2, a second address line in B2 (often empty), city in C2 and postcode in D2:
=TEXTJOIN(", ", TRUE, A2:D2)
Rows with no second address line come out clean instead of showing a , , gap.
Join with no separator at all
To run values together with nothing between them, for instance to build a code from parts, use an empty string as the delimiter:
=TEXTJOIN("", TRUE, A2:C2)
Google's page says that with an empty delimiter the text is simply concatenated.
TEXTJOIN vs JOIN vs CONCATENATE
Google links two related functions from the TEXTJOIN page and sums each up in one line:
- JOIN: "Concatenates the elements of one or more one-dimensional arrays using a specified delimiter."
- CONCATENATE: "Appends strings to one another."
Neither one-line description mentions blank cells, while TEXTJOIN's ignore_empty switch exists to handle them. For the finer differences between the three, check the JOIN and CONCATENATE help pages linked above.
Limits to know about
Google's TEXTJOIN page doesn't list a maximum result length, and it doesn't describe any difference between the desktop and mobile versions of Sheets or between personal and work accounts. If you're joining very large ranges and hit a problem, that page won't settle it.
If you're combining text as part of a bigger report, TEXTJOIN pairs well with QUERY in Google Sheets, which can filter the rows first so you only join what you need.
