COUNT NUMBER OF EXACT DUPLICATES IN A SINGLE CELL

[Nidhi007]

Kindly help me to find the total number of unique value (Say "HAPPY") in a single cell.

I use the below equation, but not getting the exact answer (Correct answer 1). 

(LEN(H4)-LEN(SUBSTITUTE(H4,"HAPPY","")))/LEN("HAPPY")

Description	Wrong	Correct
I FELT happy BECAUSE I SAW THE OTHERS WERE KHAPPY AND BEACAUSE I KNEW I SHOULD FELT HAPPY, BUT I WASN'T REALLY BEHAPPY	3	1
ACTIVE, ACTIVITY, INACTIVE, INACTIVIELY	2	1

[#2]

@Nidhi007 

You can use FILTERXML() function then UNIQUE and SUM().

=IFERROR(SUM(--(UNIQUE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,",","")," ","</s><s>")&"</s></t>","//s[preceding::*=.]"))<>"")),0)

If you want to learn more about FILTERXML() then read this article. Excel - Extract substring(s) from string using FILTERXML