|

Excel: Using TEXTJOIN with an Array

By Bill Jelen
April 1, 2018
1 comments

Six new calculations have been added since last year for Office 365 subscribers. One of those, the TEXTJOIN function, is proving to be more flexible and more powerful than it originally appeared. When TEXTJOIN was introduced in 2017, it seemed like Microsoft was trying to improve deficiencies in the CONCATENATE function. But there’s more to it.

 

In the past, if you wanted to concatenate a list of names in column A, you couldn’t use a simple formula such as =CONCATENATE(A1:A26). Instead, you had to specify each cell ­individually: =CONCATENATE(A1,A2,A3,A4,A5,…A26). Unless the values stored in column A happened to have trailing spaces, you would end up with a hard-to-read result like AndyBettyCharlieDaleEddy.

 

TEXTJOIN offers some improvements over CONCATENATE. The syntax of TEXTJOIN is =TEXTJOIN(Delimiter, Ignore_Empty, Text1, [Text2],…).

 

4Excel2018Fig1

 

Here’s how it works:

 

  • The first argument lets you specify a delimiter to appear between each value. For example, including “, ” would make sure that the names appear with a comma and space between them: Andy, Betty, Charlie, Dale, Eddy.
  • In the second argument, you can specify that empty values should be ignored. This will prevent two consecutive delimiters from appearing. Say that you removed Charlie from cell A4. By specifying TRUE for the Ignore_Empty argument, you will get Andy, Betty, Dale instead of Andy, Betty, , Dale.
  • The text arguments (Text1, Text2, etc.) can specify a range. It can be one column, like A1:A26, or multiple columns, like A1:Z26. It can even be a 3-D reference, such as ‘Jan 2018:Dec 2018’!A1:Z26.

 

SUPPORT FOR ARRAY FORMULAS

 

Array formulas are a powerful class of formulas in Excel. These formulas can perform a calculation over several cells in a range and then return the results to another function. While array formulas are powerful, they don’t work with most Excel functions. They certainly never worked with CONCATENATE.

 

While Excel Help doesn’t reveal it, the TEXTJOIN function does support an array as the third argument. This allows you to do some interesting calculations inside of TEXTJOIN. Say that you wanted to retrieve a list of people who RSVP’d “Yes” to an event.

 

You might be tempted to add a new column with =IF(B2=”Yes”,A2,””):

 

4Excel2018Fig1aWebOnly

 

In the next figure, column A contains the names. Column B contains “Yes” or “No” for RSVP. You want to find all of the people who have a RSVP value of Yes. The array portion of the formula is IF(B2:B26=“Yes”,A2:A26,“”). In English, this formula says, “If column B says Yes, then return the name in column A; otherwise return an empty text.” The consecutive quotation marks (“”) indicate an empty value.

 

4Excel2018Fig2

 

Using this array formula as the third argument to TEXTJOIN sends Andy, Betty, “”, Dale, Eddy, “”, Gloria to the TEXTJOIN function. If you specify TRUE for the Ignore_Empty argument, Excel will return Andy, Betty, Dale, Eddy, Gloria.

 

Like most array formulas, you have to use a special shortcut key. Type =TEXTJOIN(“, ”,TRUE,IF(B2:B26=“Yes”,A2:A26,“”)), but don’t press Enter. Instead, hold down Ctrl+Shift, then press Enter. Release Ctrl+Shift. This keystroke combination, called Ctrl+Shift+Enter, is required any time you enter or edit an array formula. If you do the keystroke correctly, you’ll see curly braces around the formula in the formula bar.

 

RETURNING ALL MATCHES FROM A VLOOKUP

 

The VLOOKUP function returns only the first match found in the specified lookup table. I often have people ask me if they can return all matching values.

 

If you’re looking up numbers, you can replace the VLOOKUP with SUMIF or SUMIFS. Say that you have product in A, customer in B, revenue in C. Using =VLOOKUP(“Orange”,A2:C15,2,False) will return the first customer who purchased an orange. Using =VLOOKUP(“Orange”,A2:C15,3,False) will return the price of the first order.

 

Ever since Excel 97, you could sum all of the revenue from orders for oranges using =SUMIF(A2:A15,E2,C2:C15). But there wasn’t an easy way to return a list of all of the customers who had purchased oranges.

 

By using =TEXTJOIN(“, “,TRUE,IF(A2:A15=E2,B2:B15,”“)) followed by Ctrl+Shift+Enter, you can return a list of all of the customers who purchased an orange.

 

4Excel2018Fig3WebOnly

 

SUPPORT FOR 3-D RANGES

 

I was having a casual conversation with one of the Excel team project managers who wrote the specification for the TEXTJOIN function. I was thrilled when he mentioned that TEXTJOIN would support arrays. I was unsure when I would ever have a use for 3-D references, but you can specify that you want to concatenate all of the cells in all of the sheets. For example: =TEXTJOIN(“, “,True,January:December!A1:Z26) will return any value found in cells A1:Z26 of every worksheet between (and including) January and December.

 

4Excel2018Fig4

 

Array formula techniques also can be used inside of TEXTJOIN to generate a list of dates.

 

4Excel2018Fig5

 

This figure uses a linefeed character, designated by CHAR(10) as the delimiter.

 

4Excel2018Fig6

 

 

The TEXTJOIN function is currently only in the subscription versions of Office 365. If you own a perpetual license of Excel, you’ll have to wait until Office 2019 ships later this year for the new functionality.

 

SF SAYS

 

Along with TEXTJOIN, Office 365 subscribers can also use the new IFS, MAXIFS, MINIFS, SWITCH, and CONCAT functions.

Bill Jelen is the author of MrExcel XL: 40 Greatest Excel Tips. Send questions for future articles to IMA@MrExcel.com.
1 + Show Comments

1 comment.
    David benAim April 10, 2018 AT 10:17 am

    I love textjoin!!! And using it as a “conCatenateif” is Even more magiCal. I also love that its in google sheets, the free version!

You may also like