#Excel Short and Sweet Tip #15 (Using TEXTJOIN With Non-Contiguous Ranges) by David Hager

If you look at the documentation of the TEXTJOIN function found at the following link,

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

you will find that there are multiple text arguments that are optional for TEXTJOIN. They have the same properties as the 3^rd argument, which allows for an array of strings. So, the following formula has 3 non-contiguous ranges as its last 3 arguments.

=TEXTJOIN(“,”,TRUE,A1:A10,C1:C10,E1:E10)

which affords

a,b,c,a,b,c,a,b,c,a,c,d,e,c,d,e,c,d,e,c,e,f,g,e,f,g,e,f,g,e

 

Note that this comma delimited string obtained from the TEXTJOIN function is the starting point of the technique demonstrated in this article.

https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/

So, for example, you can use non-contiguous ranges as the starting point for making an unique array or unique delimited string. Pretty neat and powerful stuff!

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.