Creating A Unique Delimited String From a Delimited String – Excel Formula Method With TEXTJOIN( David Hager)

Before I started my blog, I wrote an article about calculating the number of unique items in a delimited string. Dick Kusleika was kind enough to publish it on his web site.  Here is the link:

http://dailydoseofexcel.com/archives/2013/08/07/calculating-the-number-of-unique-items-in-a-delimited-string/

You know, It’s amazing what you can learn when you reread something you previously had written :). I was thinking of how to convert a delimited string into an array, and, lo and behold, I had already done this as part of that article. Here is that formula shown below:

DelStrArry=TRIM(MID(SUBSTITUTE(Sheet1!$A$1,”,”,REPT(“”,999)),ROW(INDIRECT(“1:”&LEN(Sheet1!$A$1)-LEN(SUBSTITUTE(Sheet1!$A$1,”,”,””))+1))*999-998,999))

Armed with this formula (which creates an array) that can be used as the main argument in the TEXJOIN formula demonstrated in my previous blog post, I was ready to complete the desired formula. See:

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

It was also necessary to correctly scale the row array the same size as the primary array. This was accomplished with the following defined name formulas.

CntDelStrArry=COUNTA(DelStrArry)

RowArry=ROW(INDIRECT(“1:”&CntDelStrArry))

The final result is:

=TEXTJOIN(“,”,,IF(MATCH(DelStrArry,DelStrArry,0)=RowArry,DelStrArry,””))

which will convert a delimited string like, for example

a,b,c,c,d,a,e,r,h,h,t,o,x,a,b,c  (in A1)

to

a,b,c,d,e,r,h,t,o,x

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