TEXTJOIN with an Array

March 06, 2018 - by Bill Jelen

One of the best new features coming in Excel 2019 is the TEXTJOIN function. If you've ever been frustrated with CONCATENATE, you will love TEXTJOIN. Today, some secret versions of TEXTJOIN.

When Excel 2019 is released in the second half of 2018, it will include a roll-up off all the new features introduced in Office 365. One of my favorites is TEXTJOIN. This function lets you concatenate a range of cells with any delimiter in between each cell.

Consider the example shown here. You have names in A2:A25. The syntax of TEXTJOIN is (Delimiter, Ignore Blank, Range).

The formula shown in A3 will join all of the names with a comma in between. This is the basic use of TEXTJOIN.

But TEXTJOIN also accepts an array of answers. With the second argument ignoring any empty cells, you can use an IF function to decide who to include. The formula shown in D7 will look at the RSVP value in B. If the person said Yes, then they are passed to TEXTJOIN. If they said no, then the IF statement sends an empty "" value. This is ignored by TEXTJOIN. Note that this formula must be finished with Ctrl+Shift+Enter and not simply Enter.

Both of the formulas discussed so far are easier than CONCATENATE where you had to specify each individual cell and each individual delimiter.

The Delimiter specified as argument 1 can be many words, creating the silly phrase shown in D14.

The other little-known version of TEXTJOIN is to accept a 3D Spearing Array. The formula shown below will take any cell value found anywhere in the four worksheets and concatenate them all.

As someone who used to use CONCATENATE a lot, TEXTJOIN is the one function that I truly miss when I am on an older version of Excel.

Getting ready for the new features in Excel 2019.

Title Photo: Paul Morris / Unsplash