Get unique values from a comma separated values in a cell

To do it with a formula it becomes a very convoluted array formula version of TEXTJOIN.

=TEXTJOIN(", ",TRUE,IF(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999)),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999)),0)=ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999)),""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode.

This basically splits the string on the comma creating an array of each part, then it iterates those arrays using MATCH to confirm which is the first and returns An array of values(if the first) or "" to the TEXTJOIN. The TEXTJOIN ignores the "" and only returns the unique list.

TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999))

Is the part that creates the array of values split on the ,

If one has the new Dynamic Array formulas we can use UNIQUE() and shorten the formula considerably:

=TEXTJOIN(", ",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW($ZZ$1:INDEX($ZZ:$ZZ,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999))))

VBA would be better suited for this.

Put this in a standard module attached to the workbook:

Function MyUniqueStr(str As String, delim As String)
    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")

    Dim strArr() As String
    strArr = Split(str, delim)

    Dim strPart As Variant
    For Each strPart In strArr
        On Error Resume Next
            dic.Add Trim(strPart), Trim(strPart)
        On Error GoTo 0
    Next strPart

    Dim temp As String
    temp = ""

    Dim key As Variant
    For Each key In dic
        temp = temp & key & delim
    Next key

    MyUniqueStr = Left(temp, Len(temp) - Len(delim))

End Function

Then one would simply use it like a normal formula:

=MyUniqueStr(A1,", ")

Or try this shorter, non-array and does not require CSE formula solution :

=TEXTJOIN(", ",1,INDEX(FILTERXML("<a><b>"&SUBSTITUTE(A1,", ","</b><b>")&"</b></a>","//b[not(preceding::*=.)]"),0))

TEXTJOIN is a new function available in Office 365

For a formula-only solution, like bosco_yip's, UNIQUE() would be needed...

So:

=TEXTJOIN(",",TRUE,
                   UNIQUE(
                          FILTERXML("<Group><Elements>"&
                          SUBSTITUTE(A1,  ", ",  "</Elements><Elements>")&
                          "</Elements></Group>","/Group/Elements")
                          )
          )

As given without it, one would just get the original back, except for the delimiter being a bare comma, not comma-space.