Excel TEXTJOIN Function Examples

Excel TEXTJOIN Function Examples

One of the newest Excel functions is TEXTJOIN — use it to combine several text items, quickly and easily. It’s a big improvement over the old CONCATENATE function, and the ampersand (&) operator. There’s a simple example below, and a fancier one, which combines TEXTJOIN with a few other functions.

TEXTJOIN Arguments

You can use TEXTJOIN if you have Excel in Microsoft 365, or Excel 2019. Or, try it in the embedded Excel File, further down on this page.

The TEXTJOIN function has 3 required arguments:

  • delimiter – A text string to separate the joined text items. Put the delimiter in double quotes, or refer to a cell that contains the delimiter
  • ignore_empty – TRUE, to ignore blank cells, or FALSE, to include them
  • text1 – The text items to join. This can be a text string, or range of cells, or an array of text strings

To get the sample files with these TEXTJOIN examples, and to see more examples, go to the Combine Text page on my Contextures site.

Video: Simple TEXTJOIN

This short video shows a couple of simple TEXTJOIN examples — how to combine weekday names, and how to combine formatted dates. There are written steps, and more examples, below the video.

Simple TEXTJOIN

In the screen shot below, there’s a simple TEXTJOIN formula in cell D3.

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

  • delimiter“, “ (command and space character)
  • ignore_empty – TRUE — ignore blank cells
  • text1 – A2:A8 — cells with the weekday names

simple textjoin formula

The formula joins all the weekday names that are in cells A2:A8, and puts a comma and space character between them. Cell A5 is blank, so it’s ignored in the formula results.

NOTE: If the formula had FALSE as the setting for ignore_empty, the blank cell would be included in the result: Sun, Mon, Tue, , Thu, Fri, Sat

Fancy TEXTJOIN

Next, we’ll look at a much fancier formula! In this example, TEXTJOIN is combined with 3 of Excel’s new dynamic array functions — FILTER, SORT and UNIQUE.

The sample file has a sales data table, with 2 years of sales data. The columns are set up as named ranges:

YrCol, RegCol, CatCol, NameCol and QtyCol.

annual sales data

Target Table

On another sheet,  we want to see who met the targets, for each year and product category. The fancy TEXTJOIN formula is in column E, Met Target, and the details are below.

sales targets table

NOTE: Dynamic arrays are available in Microsoft 365 plans, Excel for the web, and Excel mobile apps.

Formula Functions

To create the list of names in a single cell, the “Met Target” formula has 5 functions. Here’s the formula in cell E4, from the screen shot above:

=IFERROR(TEXTJOIN(“, “, TRUE, UNIQUE(SORT(FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))))), “–“)

Here’s what the 5 functions in that formula do:

  1. FILTER the rep names from the sales data, based on 3 criteria – Year, Category, Sales Target
  2. SORT the filtered rep names
  3. Return the UNIQUE rep names from the sorted list
  4. Combine the unique list of names (TEXTJOIN)
  5. Return 2 hyphens, if the result is an error (IFERROR)

FILTER Function

In that formula, the FILTER function does most of the work:

FILTER(NameCol, (YrCol=B4) *(CatCol=C4) *(QtyCol>=D4))

The FILTER function has 2 arguments:

  • array – Return rep names from the Name column (NameCol)
  • include – Return rows where all 3 criteria are met:
    1. The year is equal to the year in B4
    2. The category is equal to the category in C4
    3. The quantity is greater than or equal to the target amount in D4

TEXTJOIN

After the rep names are FILTERed, SORTed, and UNIQUE-ified, the TEXTJOIN function puts them all together.

  • TEXTJOIN(“, “, TRUE, filtered list)

The first 2 arguments are the same as in the Simple Example, and the 3rd argument is the result of the UNIQUE, SORT and FILTER functions

  • delimiter“, “ (command and space character)
  • ignore_empty – TRUE — ignore blank cells
  • text1filtered list

sales targets table

Get the Sample Files

To get the sample files with these TEXTJOIN examples, and to see more examples, go to the Combine Text page on my Contextures site.

The sample files are in xlsx format, and do not contain any macros.

Try the TEXTJOIN Function

If you don’t have TEXTJOIN in your version of Excel, try it in this embedded workbook.


________________________

Excel TEXTJOIN Function Examples

Excel TEXTJOIN Function Examples

________________________

2 thoughts on “Excel TEXTJOIN Function Examples”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.