MrExcel Publishing
Your One Stop for Excel Tips & Solutions

TEXTJOIN with an Array

March 06, 2018 - by Bill Jelen

TEXTJOIN with an Array

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.

Joining all text is easier with TEXTJOIN
Joining all text is easier with 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.

TEXTJOIN accepts 3D references
TEXTJOIN accepts 3D references

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.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Dates are whole numbers, time are decimals. Everything else is formatting."

Title Photo: Paul Morris / Unsplash

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.