I've been playing around with the TEXTJOIN and CONCAT functions this week. These are both new functions in Excel 2016, introduced in the Office 365 subscription service.
Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options.
What's nice about both of these functions is that they can handle cell ranges.
That means you can do things like this:
=TEXTJOIN(" ",TRUE,B4:H4) // J4
=CONCAT(B7:H7) // J7
Maybe you're old enough to recognize that number? :)
The ability to handle ranges is cool, because it makes it trivial to concatenate a large collection of cells with a simple formula - something that required annoying workarounds previously.
But I'm also intrigued about how this might be useful *inside* other formulas. In most programming languages, its common to split values into arrays, and join values back together again, after some kind of processing. For example, VBA has SPLIT and JOIN, and PHP has EXPLODE and IMPLODE, etc.
Inside an Excel formula, it's not too hard to split values into an array:
=MID("apple",{1,2,3,4,5},1) // returns {"a","p","p","l","e"}
But once you have {"a","p","p","l","e"}, how you can you put it back together again?
Turns out, CONCAT and TEXTJOIN will let you do it, which solves a problem that's bugged me for a long time:
=CONCAT({"a","p","p","l","e"}) // returns "apple"
=TEXTJOIN("",TRUE,{"a","p","p","l","e"}) // returns "apple"
Why does it matter?
To be honest, I'm not entirely how useful this is, since I've just started fiddling around with these functions. However, I think this might open the door to some interesting formulas that process values by looping through looping arrays. Here are a few ideas you might find interesting:
1. Uppercase text
=TEXTJOIN("",TRUE,(CHAR(CODE(MID(A1,{1,2,3,4,5},1))-32)))
The example above will uppercase "apple" > "APPLE" in A1. It's a silly example, since you can do the same thing more easily with the UPPER function. But I think it shows nicely how you can loop through each character, make changes, then bring it all back together again with TEXTJOIN.
2. Strip non-numeric characters
{=TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:100")),1)+0,""))}
Note: this is an array function - use control + shift + enter.
This example will strip all non-numeric characters in A1. So, for example, you can take a phone number like "(801)-654-4466" and turn it into "8016544466", which can then be formatted using a custom number format. You can do this same thing with the SUBSTITUTE function, but it's more work. If you're curious, here is a detailed explanation of how this formula works.
More examples of TEXTJOIN formulas.
