#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 3rd 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

 TEXTJOIN_NCR

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.

 

Advertisements

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

  1. Alex Powers

    TEXTJOIN is such a great addition to anyone’s toolbox. Unfortunately I’m stuck on Excel 2013 in the day job 😦

    Reply
  2. Pingback: #Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager | Excel For You

  3. Pingback: #Excel Super Links #45 – shared by David Hager | Excel For You

  4. Pingback: Documenting Formulas in an #Excel Range Using the FORMULATEXT, ISFORMULA and TEXTJOIN Functions by David Hager | Excel For You

  5. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s