How to count unique strings seperated by comma or semicolon in a cell

[fitzrandolph]

Greetings.

I have some dirty data that I need to count. I have a cell that has the following data and I need to count the unique strings in it. The values will be separated by a comma, semicolon or both. I cannot break these values out into multiple columns. The amount of values in the cell will vary as will the positioning of any comma or semicolon.

Example:
CELL A1|
PRD-ID-APPLE, PRD-ID-PEAR;PRD-ID-ORANGE;PRD-ID-ORANGE,PRD-ID-PEAR, PRD-ID-PINEAPPLE; PRD-ID-BANANA

In a perfect world, the formula will tell me 5 since there are 5 unique values even though there are 7 strings listed in the single cell.

My apologies if this has been answered before, I can not locate a similar question.

Many thanks.

 

[mikerickson]

How about =LEN(SUBSTITUTE(SUBSTITUTE(A1,";",","), ",,", ",")-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,";",","), ",,", ","), ",",""))

The logic is
Replace ; with ,
Replace ,, with ,

count number of ,

 

[fitzrandolph]

This is real close. But counting number of commas will count number values, some of which may be duplicates.

 

[XOR LX]

but ultimately I will need the formula / calculation to look at one single cell, count all of the unique values and spit out a number.

Wait. You didn't mistake my use of a couple of Defined Names as being formulas in other cells by any chance, did you?

I should clarify that there is only a single formula there which, as you say, "spits out a number".

Regards

 

[fitzrandolph]

While your solution looks like it would most certainly work in a normal Excel environment, I have the added difficulty of making sure the excel formula works as a calculated field in SharePoint. Unfortunately your solution uses a few techniques that is unavailable to me.

I am having some success with running the field though a calculation that cleans up the ";" with "," using FIND and REPLACE but counting the unique values between the "," is where I am coming up short.

All your effort is so much appreciated. Perhaps this problem cannot be calculated using an Excel formula within the confines of SharePoint.

https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx

 

[XOR LX]

While your solution looks like it would most certainly work in a normal Excel environment, I have the added difficulty of making sure the excel formula works as a calculated field in SharePoint. Unfortunately your solution uses a few techniques that is unavailable to me.

Might this not have been something worth mentioning earlier?

I am grateful that you are clearly appreciative of the effort I've put into this solution, though I'm sure you can understand that nobody particularly enjoys seeing an hour or so's work ultimately prove fruitless, especially if, as it turns out, that work could have been avoided, given what we know now.

Regards

 

[fitzrandolph]

I suppose you are correct.
My sincere apologies.
Your effort and time was most appreciated.
I was (and still am) under the impression that this particular problem could be handled using a variation of an Excel formula (which is why I posted in this forum) but did not want to dissuade anyone from offering assistance when associating the issue with SharePoint. This word tends to bring with it a bit of negativity.
But ultimately, you are right and I do apologize.

 

[fitzrandolph]

Excel and SharePoint go together like peanut butter and chocolate. Unfortunately there are some that are allergic to peanut butter. Those of you who are not scared off by this added layer of complexity, THANKS! Here is another site that discusses using excel in s SharePoint calculated field.

SharePoint Calculated Column Formulas & Functions - The Essential Guide | Keith Tuomi - SharePoint Server MVP

 

[XOR LX]

Thanks for the link. But can you clarify precisely what it is that you now wish of members of this forum?

Is it that you wish us to now spend the time to familiarise ourselves (assuming of course that we are not already) with the differences/similarities between Excel functions and their equivalents (or near-equivalents) in SharePoint?

And then to adapt/translate any solution that we have derived in Excel into the necessary syntax such that it also works in SharePoint?

Regards

 

[fitzrandolph]

Fair question.
Essentially my request is still the same. Is there an Excel formula that can count the number of unique values separated by commas and or semi colons in a single cell with the added caveats that the solution it cannot use an Array formula, SUBSTITUTE or MID functions. In retrospect I should have mentioned this in the original question but I only became aware of these missing functions after attempting to incorporate some of the really great answers provided.

This is a tall order, admittedly. However that is my task and I propose it to the community.

The issue with SharePoint is that it does not fully incorporate all of Excel functions, but has many of them. When developing an advanced SharePoint calculated field, I develop it fully in Excel and bring it over changing out cell values with Column names. Unless the formula uses a function that is not available to SharePoint, it will work 100% of the time. Or at least, I have yet to see it not work.

I believe this request, while unorthodox, is in the spirit of this site.

 

[XOR LX]

it cannot use an Array formula, SUBSTITUTE or MID functions

Wow! Given that those two functions are perhaps the most fundamental of all when it comes to working with strings, and with the added caveat that array-entry is forbidden, that's some challenge!

I'll have a go, though with those conditions I have to confess I'm not overly hopeful.

Regards