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:
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:
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:
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.
The final result is:
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)
Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.