excel-formulaexcel-2011

Is it possible to count the number of unique characters within a cell?


Software used: Excel Mac 2011

I have a column of cells containing alphanumeric strings, and I'm looking to count the number of unique characters that appear in each cell. I'd like to have it function as pictured below:

333333333 = 1; BEE = 2; DOG = 3; BREED = 4; APPLEPEOPLE = 5; ABC123 = 6

Because of the data I'm working with, I don't need spaces be included or excluded from the character count or any distinctions to be made between uppercase or lowercase characters.

Thanks for your help.


Solution

  • Try this:

    =SUM(IF((LEN(G13)-LEN(SUBSTITUTE(UPPER(G13),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","1","2","3","4","5","6","7","8","9","0"},""))),1,0))
    

    As stated it is quite long. This will count the English Alpha-numeric characters both upper and lower, but as helena4 pointed out, you will need to add any other symbol to the array in the formula or they will not be counted.

    Obviously change the G13 reference to the cell with the desired text to count.

    If you want a UDF use this:

    Function Uniquecount(Rng As Range) As Integer
    Dim cUnique As Collection
    Dim i As Integer
    Set cUnique = New Collection
    
    On Error Resume Next
    For i = 1 To Len(Rng)
        cUnique.Add CStr(Mid(Rng, i, 1)), CStr(Mid(Rng, i, 1))
    Next i
    On Error GoTo 0
    
    Uniquecount = cUnique.Count
    End Function
    

    Put it in a module attached to the workbook. Then call it like any other formula: =Uniquecount(G13)

    This will count everything once, including spaces.