Creating A Unique Delimited String From a Delimited String – Excel Formula Method (by 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.

Advertisements

One thought on “Creating A Unique Delimited String From a Delimited String – Excel Formula Method (by David Hager)

  1. Pingback: Calculating Aggregation for Internal Numbers From Strings in a Range by David Hager | 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